Forum Discussion

Gaspa25's avatar
Gaspa25
Regular Visitor
1 year ago
Solved

Cumulative sum dynamic

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
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

     

6 Replies

  • vivek31's avatar
    vivek31
    Resolver II
    1 year ago

    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's avatar
      Gaspa25
      Regular Visitor
      1 year ago

      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
      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.