Answered by:
DAX: Get sum of values of the last month having data
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 OrderQuantityDATESMTD('DimDate'[FullDateAlternateKey]), Select the last month of the yearALL('DimDate')) use the all in CTP3Reason 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 yax (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 year2005 1013 2352006 2677 3302007 24443 52702008 32265As 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 22005 1013 235 12/31/2005 0:002006 2677 330 12/31/2006 0:002007 24443 5270 12/31/2007 0:002008 32265 8/31/2008 0:00Marius (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 measureLeaves 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.
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 Marked as answer by Kasper de Jonge Tuesday, December 29, 2009 8:14 PM
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 Marked as answer by Kasper de Jonge Tuesday, December 29, 2009 8:14 PM

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

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 nonblank value. "
Hope this helps a little bit. Please do let us know if you still have question on this.
Thanks,
Lisa 




