Forum Discussion

kjel's avatar
kjel
Frequent Visitor
3 years ago
Solved

Calculating Cumulative sum for last year

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'[Da...
  • daXtreme's avatar
    3 years ago

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