Microsoft Office Tutorials and References
In Depth Information
FIguRE 7-50 The result of applying the LastBalance measure.
The LastBalance measure used in Figure 7-50 calculates the total of a quarter by using just the
last month available in that period. For each month, only the last date for that month is
considered. So the total of a quarter is calculated using only the last day of that quarter. You can
define the LastBalance measure in this way:
LastBalance = CALCULATE( SUM( Balances[Balance] ), LASTDATE( BalanceDate[Date] ) )
The definition of the LastBalance measure uses the LASTDATE function to keep just the last
date that is active in the current filter context. So only the last date in the selected period is
considered in the CALCULATE call.
As usual, you must use a separate Dates table. Remember that the last date in a period is the
last date available in the BalanceDate table (mentioned in the preceding formula) and not
the last date for which there is raw data. This might have unwanted consequences. If your
data does not have values for the last day of a month and the Dates table contains all the days
for that month, the LastBalance formula you have used returns no data (a blank value) for that
month. Consider the last two months available in the Balances table, as shown in Figure 7-51.
FIguRE 7-51 The last two months of balance account data.