How to show DAX Cumulative Sum of Current Year and Previous Year on same visual?

0
votes

I am trying to show the running totals (Rolling 12 months, not a calendar YTD) (cumulative sum) of revenue on the same graph (sharing the same date axis). I created a measure for the current year cumulative sum that works fine:

$CumulativeBookingRevenueCY = 
CALCULATE(
    [$Revenue],
    FILTER(
        CALCULATETABLE(
            SUMMARIZE(
                'DimDateBooking',
                'DimDateBooking'[Date]
            ),
            ALL('DimDateBooking')
        ),
        ISONORAFTER(
            'DimDateBooking'[Date], MAX(DimDateBooking[Date]), DESC
        )
    )
)

But I can't figure out what is wrong with the previous year measure. This is the code I am using:

$CumulativeBookingRevenueLY = 
CALCULATE(
    [$Revenue LY],
    FILTER(
        CALCULATETABLE(
            SUMMARIZE(
                'DimDateBooking',
                'DimDateBooking'[Date]
            ),
            SAMEPERIODLASTYEAR('DimDateBooking'[Date])
        ),
        ISONORAFTER(
            SAMEPERIODLASTYEAR('DimDateBooking'[Date]), SAMEPERIODLASTYEAR(LASTDATE(DimDateBooking[Date])), DESC
        )
    )
)

Where my revenue measures are defined as follows:

$Revenue:= CALCULATE(Sum(FactTable[Revenue]))
$Revenue LY:= CALCULATE([$Revenue], DATEADD(DimDate[Date],-1,YEAR))

This is a sample of my data (CY is working, LY is not)

Measure problem

Can someone tell me what am I missing or doing wrong? Thank you!

1
powerbidax

1 Answers

0
votes

Looks like you are missing a a few more steps.

You have:

$Revenue:= CALCULATE(Sum(FactTable[Revenue]))

$Revenue LY:= CALCULATE([$Revenue], DATEADD(DimDate[Date],-1,YEAR))

Use these additional measures below that incorporate the above measures, in your table:

revenue_last_year = IF( LASTDATE(Dates[Date]) > TODAY(), BLANK(), CALCULATE([$Revenue LY], DATESYTD(Dates[Date])))

revenue_this_year = IF( LASTDATE(Dates[Date]) > TODAY(), BLANK(), CALCULATE([$Revenue], DATESYTD(Dates[Date])))

Hope that helps!