# 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?

Frank

Thursday, October 25, 2012 7:10 PM

• 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

Friday, October 26, 2012 10:11 AM
• 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
http://brentgreenwood.blogspot.com

Friday, October 26, 2012 5:20 AM

### 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
http://brentgreenwood.blogspot.com

Friday, October 26, 2012 5:20 AM
• 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

Friday, October 26, 2012 10:11 AM
• 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