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

Answer 1

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

Answer 2

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.aspx

---

Cheers,
Jason
P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

Answer 3

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

Answer 4

Thank you both for your input!

frank

---

Frank