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.