Forum Discussion

ElliotP's avatar
ElliotP
Post Prodigy
10 years ago
Solved

Cumulative Total

Hi,   I'm at an absolute loss as to how to calculate a cumulative total. I've tried googling, reading the forums, following the documentation, decomposing the calculation, trying it as both a measu...
Need Help
Show and Tell
  • Sean's avatar
    Sean
    10 years ago

    ElliotP Sorry about the original post. It was from my phone and had typos :smileywink:

     

    Okay here is the formula for Running Total as a Calculated Column (prorerly formatted)

     

    Running Total COLUMN =
CALCULATE (
    SUM ( 'All Web Site Data (2)'[UniquePageviews] ),
    ALL ( 'All Web Site Data (2)' ),
    'All Web Site Data (2)'[Date] <= EARLIER ( 'All Web Site Data (2)'[Date] )
)

     

    And as you can see it works! :smileyhappy:

     

     

    And here's the MEASURE formula

     

    Running Total MEASURE = 
CALCULATE (
    SUM ( 'All Web Site Data (2)'[UniquePageviews] ),
    FILTER (
        ALL ( 'All Web Site Data (2)' ),
        'All Web Site Data (2)'[Date] <= MAX ( 'All Web Site Data (2)'[Date] )
    )
)

     

    Which also works...

     

LoreGaggio's avatar
LoreGaggio
Advocate I
4 years ago

CUMULATIVE SUM, date sensitive, solved at least for me:

 

BEWARE, all solutions provided before do not work if the dataset goes more in the past compared to the starting date you want to see in the Cumulative sum. Meaning, the solutions provided so far do not work if i want a Cumulative sum for Year-to-date 2022, if i have data for 2021, 2020... And i want the cumulative sum to adjust based on the year.

 

HERE A SOLUTION THAT WORKED FOR ME:

See the outcome pics below, it works even when changing year, only starting the Cumulative Sum from the beginning of the selected year!

 

The measure is essentially a conditional calculation between the first day of the selected year and the last day of the current period (e.g. month):

 

Nb of ship-to Cumulative Total =
IF([Nb of ship-to]<>0,
CALCULATE([Nb of ship-to],
DATESBETWEEN('Calendar'[Date], DATE(YEAR(MIN('Calendar'[Date])),1,1), 'Calendar'[LastDate])), "")

 

The IF function is only there to make sure i see blank cells in the months Year-to-go.

The LastDate measure is calculated as:

LastDate = Lastdate('Calendar'[Date])

 

 

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

Hello!

Something similar happens to me, I am looking for a function that shows me the accumulated per month for 2023 taking into account the values of 2020, 2021 and 2022, as follows:

But I do not manage to make January start with the accumulated values of previous years and that from there it accumulates

Will you know how?

By the way, I found the function of "IF([]),<>0, "") very useful. Thank you!!