Calculating Cumulative sum for last year

[kjel]

I'm using the following measure to get the cumulative totals for the periods a user has selected:

Sales CumSum =

CALCULATE(

    [Sales Amount],

    FILTER(ALLSELECTED('Date'),

    'Date'[DateTime] <= MAX('Date'[DateTime])

))

So if a user selects August, September and October 2022, they get a running total starting at 0. I would like to be able to create a measure to calculate last year's numbers as well, so that if a user selects the period August-October 2022, they get the cumulative 2021 total for these months.

[daXtreme · Accepted solution]

Well, you have to shift the dates one year back. So, something like:

CALCULATETABLE(
    // This will move the entire
    // selected period one year back.
    DATEADD(
        'Date'[DateTime],
        -1,
        YEAR
    ),
    VAR MaxDate =
        MAX( 'Date'[DateTime] )
    VAR CurrentSelection =
        FILTER(
            ALLSELECTED( 'Date' ),
            'Date'[DateTime] <= MaxDate
        )
    RETURN
        CurrentSelection
)

Put this as the filter under your CALCULATE instead of your FILTER. This will be the running total for the same months but 1 year back. If you want to get the sum for all the selected months, then change your FILTER to be:

CALCULATETABLE(
    // This will move the entire
    // selected period one year back.
    DATEADD(
        'Date'[DateTime],
        -1,
        YEAR
    ),
    ALLSELECTED( 'Date' )
)

[ashfaq205]

can you please explain it as i am unable to understand and i have asked the same question. will really be helpful if you could.

I have asked the same question in my this question

https://community.powerbi.com/t5/Service/How-to-cumulative-sum-for-previous-month-amp-previous-year-...

[kjel]

That worked perfectly, thanks a lot!