none
Previous month Total

    Question

  • If I have a PowerPivot Table and the following DAX Measure works correctly for calculating the Total for the current Month.

    =CLOSINGBALANCEMONTH(TOTALMTD(sum(vwGSMBig2[XCASE]),vwGSMBig2[CrDate]),vwGSMBig2[CrDate])

    Why does the following not work to find the Total for the previous month?

    =CLOSINGBALANCEMONTH(TOTALMTD(sum(vwGSMBig2[XCASE]),PREVIOUSMONTH(vwGSMBig2[CrDate])),previousmonth(vwGSMBig2[CrDate]))

    Wednesday, March 14, 2012 4:33 PM

Answers

  • Hi David,

    Please pay attention to the difference between Opening balance and Closing balance, these functions always return the value of an expression evaluated for a specific point in time. The point in time we care about is always the last possible date value in a calendar period. The opening balance is based on the last date of the previous period, while the closing balance is based on the last date in the current period. The current period is always determined by the last date in the current date context. 

    For more information about CLOSINGBALANCEMONTH function please see: http://msdn.microsoft.com/en-us/library/ee634215(SQL.105).aspx

    Thanks,


    Challen Fu

    TechNet Community Support

    Monday, March 19, 2012 6:11 AM
    Moderator

All replies

  • Hi David,

    Please pay attention to the difference between Opening balance and Closing balance, these functions always return the value of an expression evaluated for a specific point in time. The point in time we care about is always the last possible date value in a calendar period. The opening balance is based on the last date of the previous period, while the closing balance is based on the last date in the current period. The current period is always determined by the last date in the current date context. 

    For more information about CLOSINGBALANCEMONTH function please see: http://msdn.microsoft.com/en-us/library/ee634215(SQL.105).aspx

    Thanks,


    Challen Fu

    TechNet Community Support

    Monday, March 19, 2012 6:11 AM
    Moderator
  • Thanks For the Reply Challen

    So are you saying that to find the Closing Balence of the previous month I should be actually looking for the opening balance of this month? if so

    why or where would you use the pervios month function

    Monday, March 19, 2012 3:43 PM
  • Dave,

    Is this still an issue?

    Thank you!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, October 28, 2013 7:21 AM
    Owner