locked
How ClosingBalanceMonth DAX function work at Quarter and Year level? RRS feed

  • Question

  • How ClosingBalanceMonth DAX function work at Quarter and Year level? I was writing DAX formula for closing balance calculation and notice that after I properly define calculation for ClosingBalanceMonth, I did not need to write different calculation for quarter and year level. I noticed that at quarter level function correctly used last month of the quarter balanace and for year level calculation used last month of year balance. Could you please explain how ClosingBalanceMonth function works when I calculated year level cell? There are very few details available in documentation now.

    I used following formula for my calculation:

    =IF(IsError(VALUES('Date'[Date])),CLOSINGBALANCEMONTH(SUMX('stocks', 'stocks'[ClosingValue]), 'stocks'[date]),SUMX(Stocks, Stocks[ClosingValue]))

    I used 2 tables in my source:
  • Stocks - list of closing stock price for each day. Important fields are Stock_symbol, Date and ClosingValue.
  • Date - list of date attributes. Important fields are Date, Month, Quarter and Year.

    Thank you,
  • Vidas Matelis


    http://www.ssas-info.com http://powerpivot-info.com
Monday, November 30, 2009 2:32 AM

Answers

  • Hi, Vidas, As per the Books On Line for PowerPivot, CLOSINGBALANCEMONTH "Evaluates the specified expression at the calendar end of the given month. The given month is calculated as the month of the latest date in the dates argument, after applying all filters." So if the current context is a year, the closing balance month is the last month of the year. If the current context is a quarter, the closing balance month is the last month of the quarter. Please let us know if you have any further question on this. Thanks! Lisa
    • Marked as answer by Vidas Matelis Monday, November 30, 2009 9:02 PM
    Monday, November 30, 2009 8:47 PM