Cumulative sum dynamic

[Gaspa25]

Hi everyone, 

I am trying to create a measure that calculates the cumulative sum of a column, I want the cumulative sum to update based on the selected months in my slicer, meaning I want my graph to update based on months selected and to do the cumulative sum of only the selected moths, i.e. if I select only June and July it should to the cumulative sum of only those 2 months. 

I have created this measure and looked around for tips but I was unable to achieve this goals everytime either it sum ignoring the filter or it gives back the sum per day, and not the cumulative sum.

p.s the objective is to have the measure to show the cumulative sum per per period selected and to create a new measure that does the same per prior period so that I can compare the values for this and prior year. 

here is the measure: 

YTD =

CALCULATE(SUM('RentalAgreement List'[Total R.A]),

FILTER(ALL(DateTable[Date]), DateTable[Date]>=MIN(DateTable[Date]) && DateTable[Date]<=MAX(DateTable[Date])))


background rentalagreement list is the table where I have my sales per day, date is a table I have created to order sales.

If anyone could help me to figure out how to achieve this measure both for current year and prior year same period.

Thank you in advance

[vivek31]

HI @Gaspa25 ,

you can try this type of measure to find your requirement according cumulative sum.

cumulative sum = CALCULATE(SUM(Sales[SalesAmount]),
                            FILTER(ALLSELECTED('Calendar 2'),
                            'Calendar 2'[DateKey] <= MAX('Calendar 2'[DateKey])))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gaspa25]

Hi, 

Thank you for the help, that code does actually work to measure for current year cumulative sum, I would like to calculate as well the cumulative sum for the sameperiod for last year so that when I select june and july 2024 it also shows june and july 2023, but if i try to do the measure 

ytd test 3 ly = CALCULATE([YTD TEST 3], SAMEPERIODLASTYEAR(DateTable[Date].[Date]))

it only shows the sum per day and not the cumulative, do you know the solution for this one?

thank you in advance

[vivek31]

HI @Gaspa25 

you can use this measure to find sum for the sameperiod for last year cumulative sum

l_y cumulative sum = 
var total_running = CALCULATE([cumulative sum],
                     SAMEPERIODLASTYEAR(DATESMTD('Calendar 2'[DateKey])))
RETURN
total_running

[Anonymous]

Hi @Gaspa25 ,

@vivek31 's workaround is great.

For returning the sum of this year and last by selecting the months, you could create a new month table and create below measures.

Month = DISTINCT(SELECTCOLUMNS('DateTable',"Month",FORMAT([Date],"MMM"),"SORT",MONTH([Date])))

This Year = CALCULATE(SUM('RentalAgreement List'[Total R.A]),FILTER(ALLSELECTED('RentalAgreement List'),YEAR([Date])=YEAR(TODAY())))

Last Year = CALCULATE(SUM('RentalAgreement List'[Total R.A]),FILTER(ALLSELECTED('RentalAgreement List'),YEAR([Date])=YEAR(TODAY())-1))

 

You can download my attachment for more details.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gaspa25]

Hi @Anonymous,

thank you for your suggestion, unfortunately that does not achieve the goal, as I wanted to display graphically the progressive cumulative sum for current and prior year for the selected period, see image below. 

Any idea on how to achieve that? 

[Anonymous · Accepted solution]

Hi @Gaspa25 ,

I missed something in my formulas. Please use these:

2023 YTD = CALCULATE(SUM('RentalAgreement List'[Total R.A]),FILTER(ALLSELECTED('RentalAgreement List'),[sort]<=MAX('Month'[SORT])&&YEAR([Date])=2023))

2024 YTD = CALCULATE(SUM('RentalAgreement List'[Total R.A]),FILTER(ALLSELECTED('RentalAgreement List'),[sort]<=MAX('Month'[SORT])&&YEAR([Date])=2024))

Here's the result:

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.