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

     

Sean's avatar
Sean
Community Champion
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...

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Sean ,

I am also trying to create a cumulative total of the past dates and i have two conditions :

1) if delivery date <=Today() then it should populate the cumulative total of Quantity column untill today's date .

2) If delivery date >Today then it should give me only the value which is present in the delivery column.
I have tried creating Measure but its giving me Cumulative total of whole data however my expected result is below :
Measure 1

Sales YD = CALCULATE([sum_of_MGO1],
FILTER(ALLSELECTED(ZIBP_PO_MGRTN),
ZIBP_PO_MGRTN[DELIVERY_DATE]<=MAX(ZIBP_PO_MGRTN[DELIVERY_DATE])))





My desrire result should be like this :


I have tried couple of more meaure but it has Date column and i am not able to use any min, max, selectedvalue while taking date and due to that my If condition is not wokring :
Measure 2(Not working) but when i created Column using below formula its calculating the Sum of whole  Column(MNGO1)

check_2_YTD =
VAR beforeToday =
CALCULATE(
    [sum_of_MGO1],
    FILTER(ALLSELECTED(ZIBP_PO_MGRTN),
    ZIBP_PO_MGRTN[DELIVERY_DATE] <= MAX(ZIBP_PO_MGRTN[DELIVERY_DATE])))

VAR afterToday =
CALCULATE(
    [sum_of_MGO1],
    FILTER(ALLSELECTED(ZIBP_PO_MGRTN),
    ZIBP_PO_MGRTN[DELIVERY_DATE] > MAX(ZIBP_PO_MGRTN[DELIVERY_DATE])))


RETURN
IF(ZIBP_PO_MGRTN[DELIVERY_DATE]<=TODAY(),beforeToday,afterToday)



I really appreicate if you can help me out in getting this result .

 

Thanks,

Ashish