Microsoft Office Tutorials and References
In Depth Information
FIguRE 7-53 The balance data at end of month, quarter, and year for each month.
The formulas used to calculate ClosingBalanceMonth, ClosingBalanceQuarter, and
ClosingBalanceYear measures are the following:
ClosingBalanceMonth = CLOSINGBALANCEMONTH( SUM( Balances[Balance] ), BalanceDate[Date] )
ClosingBalanceQuarter = CLOSINGBALANCEQUARTER( SUM( Balances[Balance] ), BalanceDate[Date] )
ClosingBalanceYear = CLOSINGBALANCEYEAR( SUM( Balances[Balance] ), BalanceDate[Date] )
These formulas use the LASTDATE function internally, but they operate on a set of dates that
can extend the current selection in the PivotTable. For example, the CLOSINGBALANCEYEAR
function considers the LASTDATE of Balance[Date], which is applied to the last year period of the
dates included in the filter context. So for February 2010 (and for any month or quarter of 2010),
this date is December 31, 2010. The CLOSINGBALANCEYEAR function behaves like a CALCULATE
expression using the ENDOFYEAR function as a filter. As usual, the use of CALCULATE is more
generic and flexible, but specific DAX functions like CLOSINGBALANCEYEAR better express the
intention of the measure designer. The following are measures equivalent to the ones previously
shown using CALCULATE syntax.
ClosingBalanceEOM = CALCULATE( SUM( Balances[Balance] ), ENDOFMONTH( BalanceDate[Date] ) )
ClosingBalanceEOQ = CALCULATE( SUM( Balances[Balance] ), ENDOFQUARTER( BalanceDate[Date] ) )
ClosingBalanceEOY = CALCULATE( SUM( Balances[Balance] ), ENDOFYEAR( BalanceDate[Date] ) )
Tip The DAX functions OPENINGBALANCEMONTH, OPENINGBALANCEQUARTER, and
OPENINGBALANCEYEAR use the FIRSTDATE internally instead of the LASTDATE of the
considered period. They correspond to the CALCULATE formula, which uses STARTOFMONTH,
STARTOFQUARTER, and STARTOFYEAR internally as its filter, respectively.