cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Anonymous
Not applicable

## CALCULATE CUMULATIVE SUM OVER THE PERIOD OF 1 YEAR

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?

1 ACCEPTED SOLUTION
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
3 REPLIES 3
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

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.

If this post helps, then please consider Accept it as the solution to help the other members find it.
Anonymous
Not applicable

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

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

Cheers