Calculate annual total from monthly total using Measure in Power BI

Question

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?

Leonard Leonard · Accepted Answer · 2017-01-13T18:09:53

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).

Calculate annual total from monthly total using Measure in Power BI

1 Answers