Calculate annual total from monthly total using Me

问题:

I want to calculate annual revenue for each month from monthly revenue data. Sample data is shown as below:

e.g. For annual revenue of 2015 May = sum of monthly revenue from 2015 Jan to 2015 May, and so on.

The problem is, the Monthly Revenue is a measure. I want to create a measure for Annual Revenue as well, so that it can interact with other filters. However, I only know how to write the expression using Calculated Column:

Annual Revenue = 
CALCULATE(
    [Monthly Revenue],
    FILTER(
        'Month',
        'Month'[Year] = EARLIER('Month'[Year]) &&
        'Month'[MonthKey] <= EARLIER('Month'[MonthKey])
    )
)

How can I translate the above expression so that it will work with Measure?

回答1:

It sounds like what you want is a YTD measure for any given date (i.e. in May 2015, YTD is January-April 2015). I typically wouldn't do this using a [Monthly Revenue] measure and a Month table. I'd do this using a regular date table, a base Revenue measure, and DATESYTD.

However, using the MONTH table as you've outlined, this is what I'd do for a measure:

Annual Revenue Measure =
CALCULATE (
    [Monthly Revenue],
    FILTER (
        ALL ( 'Month' ),
        'Month'[Year] = MAX ( 'Month'[Year] )
            && 'Month'[MonthKey] <= MAX ( 'Month'[MonthKey] )
    )
)

You'll note it's almost the same as you have for your calculated column, except using MAX rather than EARLIER (since EARLIER only applies to calculated columns).