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 t...
  • 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.

     

     

vivek31's avatar
vivek31
Icon for Resolver II rankResolver 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.

     

    • Gaspa25's avatar
      Gaspa25
      Regular Visitor
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        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.

         

         

  • vivek31's avatar
    vivek31
    Icon for Resolver II rankResolver II
    1 year ago

    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