none
DAX: Get sum of values of the last month having data RRS feed

  • Question

  • I'm trying to get the sum of a measure on the last month of a year where production happened for a blog post. I started this discussion on Rob Collie's excellent blog but decided to continue here to get to get more people involved (and Rob and Marius are also spotted here). I try to look from the Business user perspective and don't want to use any SQL commands or MDX just DAX or other PowerPivot functions.

    I use the Adventureworks database and loaded Dimdate and Factinternetsales complete (no filtering). I then created a measure to determine the sum of orderquantity in the last month of a year with the following syntax:
    =CALCULATE(sum('FactInternetSales'[OrderQuantity]),     Get the sum of OrderQuantity
    DATESMTD('DimDate'[FullDateAlternateKey]),                   Select the last month of the year
    ALL('DimDate'))                                                              use the all in CTP3

    Reason we need to use the ALL('DimDate') (from the PowerPivotPro thread) by MSFT Howie: 
    When using Time intelligence functions like LASTDATE or DATESMTD, in CTP3, it is necessary to add another argument which is ALL (TimeTable). This won’t be needed post CTP3. Without this, only the selected dates are considered, and you can’t find the last month unless you are in the last month. 

    Ok here comes the problem, when you put this calculated measure in a Pivot with year on an the y-ax (you need a time dimension to use a time intelligent function) the function works like it should (conform the specs), it shows me the sum of the last month in a year. This gives me:

    Row Labels Sum of OrderQuantity Total last month of year 
      2005          1013                              235 
      2006          2677                              330 
      2007          24443                            5270 
      2008          32265

    As you can see 2005,2006 and 2007 work like i wanted but 2008 is empty, and this is because my date dimension had values until 8/31/2008 while my data has values until 7/31. This is not what i want, i want the last value of a month that has production values. The problem is in the underlying function of DATESMTD being:
    DatesBetween (Date_Column, StartofMonth (LastDate (Date_Column)), LastDate (Date_Column)) (from bol) It uses the lastdate taken from the time dimension. 

    Which gives:

    Row Labels Sum of OrderQuantity Total last month of year Measure 2 
      2005             1013                                 235                      12/31/2005 0:00 
      2006             2677                                 330                      12/31/2006 0:00 
      2007             24443                                5270                    12/31/2007 0:00 
      2008             32265                                                           8/31/2008 0:00

    Marius (MSFT) suggested 3 options to solve this problem on the PowerPivotPro post:
    1. Do nothing – the results are correct, since there were no sales in the last month of 2008 (August).
    2. Use LastNotBlank() instead of LastDate(), if that’s the actual intention of the calculation.
    3. Remove/filter out the August 2008 rows from DimDate when importing that table, if those rows weren’t supposed to be there in the first place.
    Option 1 and 3 are not an option in my scenario because 1) I understand this is correct conform specs but not to my scenario  3) i want to have all data available for other measure

    Leaves option 2: But i don't have a clue how to implement this, i tried this function:
    =LASTNONBLANK('DimDate'[FullDateAlternateKey], sum('FactInternetSales'[OrderQuantity]) > 0)
    But this gives exactly the same result as Lastdate. I understand i use lastnonblank on the date dimension and that won't help me, i want to get the last date having a value in OrderQuantity from factinternetsales. 

    Anyone has a clue how to get this working using DAX? I suspect this will have to be used a lot in the real world. Most DWH's will have a prefilled date dimension until way in the future. 

    You can download the PowerPivot app here if you want to try yourself.

    Thanks in advance for thinking with me.
    Tuesday, December 29, 2009 2:20 PM

Answers

  • Hi, Kasper,

    Based on Howie's suggestion, I changed the filter condition in your LASTNONBLANK function to below and it starts to return the expected result in my test. Can you test this out and let me know if this is what you are looking for?

    LASTNONBLANK('DimDate'[FullDateAlternateKey], countrows( RelatedTable('FactInternetSales')))

    Thanks,
    Lisa

    Tuesday, December 29, 2009 7:50 PM
    Moderator

All replies

  • Hi, Kasper,

    Based on Howie's suggestion, I changed the filter condition in your LASTNONBLANK function to below and it starts to return the expected result in my test. Can you test this out and let me know if this is what you are looking for?

    LASTNONBLANK('DimDate'[FullDateAlternateKey], countrows( RelatedTable('FactInternetSales')))

    Thanks,
    Lisa

    Tuesday, December 29, 2009 7:50 PM
    Moderator
  • Phew incredible, Thank you Lisa.... it works! I spend a few hours figuring out how to fix it. 

    What you do in the expression with countrows( RelatedTable('FactInternetSales'))) is: give me the lastnonblank where i have rows in the relatedtable FactInternetSales?

    How does this work? i tried changing it to LASTNONBLANK('DimDate'[FullDateAlternateKey], countrows( RelatedTable('FactInternetSales')) > 0) but that doesn't return the result i want. Does the filter works like somekind of Having from SQL? Could you explain it ?

    Thank you,
    Kasper
    Tuesday, December 29, 2009 8:14 PM
  • Hi, Kasper,

    I believe what confused us was the type of expression used in the LASTNONBLANK. The key is it needs to be an expression return null/blank. It should not be a Boolean expression evaluated to true or false. In either of your examples, because the expression is a Boolean expression and it will never be null/blank. 

    BOL does mention this but without the trials we have gone through, it might not be very clear what it really meant:  

    "When the optional argument, expression, is supplied, the function returns the last date where the conditions in the expression return a non-blank value. "

    Hope this helps a little bit. Please do let us know if you still have question on this.

    Thanks,
    Lisa

    Tuesday, December 29, 2009 10:00 PM
    Moderator
  • Hi Lisa,

    Thanks for the explanation, makes sense when I read the description.
    I think the sample made me think it was a boolean:
    =LASTNONBLANKDATE([OrderDate],[SalesRegion] = "USA")

    Thanks,
    Kasper

    Wednesday, December 30, 2009 1:33 PM
  • Hi Kasper and Lisa,
    Thanks for continuing this thread and determining where we can be more helpful with our documentation. We are now reviewing the LASTNONBLANK function reference topic and will use your comments to help us.
    Regards,
    Nathaniel

    Wednesday, December 30, 2009 2:05 PM
  • Hi Nate,

    You are welcome, PowerPivot will only be a success when it will be easy to use to BI Powerusers. Documentation is imho one of the most important things that will contribute to that, so good luck :)

    Regards,
    Kasper
    Wednesday, December 30, 2009 3:33 PM
  • Please also address the issue of the expression being "optional." In every case I've attempted to use LASTNONBLANK, I get an error if an expression isn't included in the formula.
    Wednesday, December 30, 2009 5:23 PM
  • Hi Rodolfo,

     

    What excatly did you want to know ? 

    I could explain what the function does:

    =LASTNONBLANKDATE([OrderDate],[SalesRegion] = "USA")

    gives us the last date from the Orderdate column where the salesregion is "USA". 

    Monday, August 30, 2010 9:58 AM
  • Thanks, very helpful, 6 years later! I was able to put this to immediate use.
    Tuesday, March 29, 2016 11:46 PM