Answered by:
working with PREVIOUSMONTH

Question
-
I have the following DAX expression
Prior Month:=CALCULATE(SUM([SalesAmount]),PREVIOUSMONTH('DimDate'[Date]))
it works great on the month level, but I want the value to be blank if they are at any other level within a hierarchy (Calendar YQMD). How can I modify the calculation to do this?
thanks in advance
Frank
Thursday, October 25, 2012 7:10 PM
Answers
-
It is safer to use the ISFILTERED function rather than use the countrows(values()) function. For eg, if we have a year which has just one month, then the above expression will return 1 at the year level also as well as the month, and hence the value will be displayed at both yera and month level. this is highly improbable in Date dimension scenarios but still keeping this for future reference.
=IF(ISFILTERED(DimDate[MonthNumber]),([SumAmount_PrevMonth]) ,BLANK() )
http://beyondrelational.com/modules/24/syndicated/477/Posts/11082/detecting-total-subtotal-and-hierarchy-levels-in-powerpivot.aspxCheers,
Jason
P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)- Proposed as answer by Brent GreenwoodEditor Friday, October 26, 2012 2:48 PM
- Marked as answer by Frank Kearney Tuesday, October 30, 2012 4:51 PM
Friday, October 26, 2012 10:11 AMAnswerer -
Hi Frank -
One way to accomplish this is by using the IF VALUES pattern. Check out Rob Collie's blog here for a thorough write-up and lots of examples. To summarize, you inspect the filter context to see if only a single month exists. Meaning the calculation is at or below the month level. If only 1 month exists, then return your previous month calc, otherwise return blank.
=IF( COUNTROWS(VALUES(DimDate[MonthNumber])) = 1 ,([SumAmount_PrevMonth]) ,BLANK() )
Hope that helps.
Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com- Edited by Brent GreenwoodEditor Friday, October 26, 2012 5:22 AM
- Marked as answer by Frank Kearney Tuesday, October 30, 2012 4:52 PM
Friday, October 26, 2012 5:20 AMAnswerer
All replies
-
Hi Frank -
One way to accomplish this is by using the IF VALUES pattern. Check out Rob Collie's blog here for a thorough write-up and lots of examples. To summarize, you inspect the filter context to see if only a single month exists. Meaning the calculation is at or below the month level. If only 1 month exists, then return your previous month calc, otherwise return blank.
=IF( COUNTROWS(VALUES(DimDate[MonthNumber])) = 1 ,([SumAmount_PrevMonth]) ,BLANK() )
Hope that helps.
Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com- Edited by Brent GreenwoodEditor Friday, October 26, 2012 5:22 AM
- Marked as answer by Frank Kearney Tuesday, October 30, 2012 4:52 PM
Friday, October 26, 2012 5:20 AMAnswerer -
It is safer to use the ISFILTERED function rather than use the countrows(values()) function. For eg, if we have a year which has just one month, then the above expression will return 1 at the year level also as well as the month, and hence the value will be displayed at both yera and month level. this is highly improbable in Date dimension scenarios but still keeping this for future reference.
=IF(ISFILTERED(DimDate[MonthNumber]),([SumAmount_PrevMonth]) ,BLANK() )
http://beyondrelational.com/modules/24/syndicated/477/Posts/11082/detecting-total-subtotal-and-hierarchy-levels-in-powerpivot.aspxCheers,
Jason
P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)- Proposed as answer by Brent GreenwoodEditor Friday, October 26, 2012 2:48 PM
- Marked as answer by Frank Kearney Tuesday, October 30, 2012 4:51 PM
Friday, October 26, 2012 10:11 AMAnswerer -
Good point Jason.
So Frank, if you're working in PowerPivot2012, go with ISFILTERED. But if you're still in PowerPivot2008R2, the IF(VALUES) approach will work well for date hierarchies, as long as you are using a proper date dimension in your model with full years populated.
Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com- Edited by Brent GreenwoodEditor Friday, October 26, 2012 2:49 PM
Friday, October 26, 2012 2:48 PMAnswerer -
Thank you both for your input!
frank
Frank
Tuesday, October 30, 2012 4:52 PM