none
dax measures

    Question

  • Hi,

    I have a tabular project with two facts and DimDate. In each fact I have a simple measure and I need to build an excel report with months names in the columns and a single measure in the rows. The measure should represent Fact1 measure if the month is the next month, otherwise it should be Fact2 measure .

    I fried to build a third measure in the tabular with IF function with date column condition but it did not work. 

    Would appreciate any help,

    Thanks,

    Sasha


    • Edited by Sasha R Wednesday, April 09, 2014 5:29 AM
    Tuesday, April 08, 2014 1:15 PM

Answers

  • You could try something like the following:

    =SUMX(DimDate, if(month(DimDate[PreviousMonth] = month(today()) && year(DimDate[PreviousMonth] = year(today()), 'Fact1'[sum1], 'Fact'[sum2]))))

    The SUMX will iterate over all the date rows which will give a row context in which the value of the month and year can be determined.

    Note that this will only return Sum1 for the current month, all months before and after the current month will return the value for sum2 


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Sasha R Thursday, April 10, 2014 11:49 AM
    Wednesday, April 09, 2014 7:42 AM

All replies

  • Hi Sasha,

    What reference point are you using to determine whether the month is the next month? For example, are you basing it on the last month in your model that has data in Fact2? Maybe sharing the DAX formula that you tried will help us to understand better...


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn


    Tuesday, April 08, 2014 2:06 PM
  • Hi, thanks fro the reply.

    I have a date column in both facts connected to the date dim. I made a column in the dim with the previousmonth function and also a measure in one on the facts: if(month(DimDate[PreviousMonth] = month(today()) && year(DimDate[PreviousMonth] = year(today()), 'Fact1'[sum1], 'Fact'[sum2]))).

    some thing like this..

    But he can not determine the column of the date...

    Wednesday, April 09, 2014 6:13 AM
  • You could try something like the following:

    =SUMX(DimDate, if(month(DimDate[PreviousMonth] = month(today()) && year(DimDate[PreviousMonth] = year(today()), 'Fact1'[sum1], 'Fact'[sum2]))))

    The SUMX will iterate over all the date rows which will give a row context in which the value of the month and year can be determined.

    Note that this will only return Sum1 for the current month, all months before and after the current month will return the value for sum2 


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Sasha R Thursday, April 10, 2014 11:49 AM
    Wednesday, April 09, 2014 7:42 AM
  • Thanks Darren,

    Can I use the function if  Fact1 is not connected to the DinDate? I just want an unfiltered sum for the current month and I get a huge number... The other months are working great!

    Wednesday, April 09, 2014 9:43 AM
  • Thanks Darren,

    Can I use the function if  Fact1 is not connected to the DinDate? I just want an unfiltered sum for the current month and I get a huge number... The other months are working great!

    If there is no relationship then DimDate will not filter the measure. If you can create a relationship the previous calc should start working.

    If you can't create a relationship because the grain is different then you can use a technique similar to the one Alberto blogged about here http://www.sqlbi.com/articles/budget-and-other-data-at-different-granularities-in-powerpivot to create a measure over Fact1 which you could then use in the other calculation.

    It's hard to answer in any more detail without knowing what your schema looks like and why there is not a relationship between DimDate and Fact1. 


    http://darren.gosbell.com - please mark correct answers

    Wednesday, April 09, 2014 8:49 PM