locked
working with PREVIOUSMONTH RRS feed

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

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

    Some Random Thoughts

    Follow me on Twitter

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


    Friday, October 26, 2012 5:20 AM
    Answerer

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


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

    Some Random Thoughts

    Follow me on Twitter

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


    Friday, October 26, 2012 2:48 PM
    Answerer
  • Thank you both for your input!

    frank


    Frank

    Tuesday, October 30, 2012 4:52 PM