Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

DAX formula Running Total for previous year

I have the following measure for calculating the running total of customers.

Running Total Customers:=
CALCULATE(
COUNT('Customers'[CustomerId]);
FILTER(ALL(Date[CalendarDate]);Date[CalendarDate]<=MAX(Date[CalendarDate])
))

Works like a charm. Now i want to have a measure showing what the running total was the same period last year. i have not been able to solve this.

For example:

Year, RTC, RTCLY
2010, 66, 
2011, 77, 66
2012, 79, 77
2013, 90, 79

Please advice

  • v-haibl-msft's avatar
    v-haibl-msft
    10 years ago

    Anonymous

     

    Please try with following measure for RTCL. And I create a new “Calendar Year” column in Date table.

    CalendarYear = YEAR ( 'Date'[CalendarDate] )
    Running Total Customers LY = 
CALCULATE (
    COUNT ( 'Customers'[CustomerId] ),
    FILTER (
        ALL ( 'Date'[CalendarDate] ),
        'Date'[CalendarDate] <= MAX ( 'Date'[CalendarDate] )
            && YEAR ( 'Date'[CalendarDate] ) < MAX ( 'Date'[CalendarYear] )
    )
)

     

    Best Regards,
    Herbert

1 Reply

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    10 years ago

    Anonymous

     

    Please try with following measure for RTCL. And I create a new “Calendar Year” column in Date table.

    CalendarYear = YEAR ( 'Date'[CalendarDate] )
    Running Total Customers LY = 
CALCULATE (
    COUNT ( 'Customers'[CustomerId] ),
    FILTER (
        ALL ( 'Date'[CalendarDate] ),
        'Date'[CalendarDate] <= MAX ( 'Date'[CalendarDate] )
            && YEAR ( 'Date'[CalendarDate] ) < MAX ( 'Date'[CalendarYear] )
    )
)

     

    Best Regards,
    Herbert