Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
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
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
v-eachen-msft
Community Support
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.

 

Community Support Team _ Eads
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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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