cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors