CALCULATE CUMULATIVE SUM OVER THE PERIOD OF 1 YEAR

[Anonymous]

Hi all,

I'm trying to measure an cumulative sum over time that only takes data within a year.

From this post, i got this code:

Measure =
CALCULATE (
    SUM ( Table1[Amount] ),
    FILTER ( ALLSELECTED ( Table1 ), Table1[Date] <= MAX ( DateHelper[Date] ) )
)

But i would like to SUM the Table1[Amont] considering only the data within the last year (Date - 365 days). Can someone help me on this subject?

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table and build a relationship from the Date column of Table1 to the Date column of the Calendar Table.  In the Calendar Table, create this calculated column formula to extract the Year: Year = Year(Calendar[Date]).  To your slicer/visual/filter, drag Year from the Calendar Table.  Write these measures

Total amount = SUM ( Table1[Amount] )

Total amount last year = CALCULATE([Total amount],PREVIOUSYEAR(Calendar[Date]))

Hope this helps.

[v-eachen-msft]

Hi @Anonymous ,

You could replace MAX ( DateHelper[Date] ) with (MAX ( DateHelper[Date] )-365) in your filter formula.

Or you could use SAMEPERIODLASTYEAR() function to get data of previous year.

[Anonymous]

Hi Hsneto - you want to try something like this.....

Sales Total =
CALCULATE (
    [measure],
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -1, YEAR )
)

Cheers