Cumulative Totals for measures

[Alienvolm]

Hello, 

I need some help for a problem I've been trying to solve in many different ways, but apparently I cannot manage to create a cumulative column for an inventory file.

This is the 'BOOST EOP' Table as in origin: 

I have added the following Calculated Columns: 

Active (BOOST), Suspended and Terminated are simple conditional columns where I consider Active whatever doesn't have a Suspension or Termination date. 

Date of Reference is the delimitation to consider a license active (if there is no suspend date or end date, the date returned is today; if there is a suspend date, the date returned is Service Suspend Date; if there is an end date, the date returned is Service End Date). 

I have then created the following measures: 

Test Suspended = CALCULATE ( 

    SUM ([Suspended]), 

    FILTER ( 

        ALL ('BOOST EOP'[Service Start Date]),  

        'BOOST EOP'[Service Start Date] <= MAX ([Date Of reference])  

    ) 

) 

Test Ended = CALCULATE ( 

    SUM ([Terminated]), 

    FILTER ( 

        ALL ('BOOST EOP'[Service Start Date]),  

        'BOOST EOP'[Service Start Date] <= MAX ([Date Of reference])  

    ) 

) 

Test Active = [Sum of Quantity]-[Test Suspended]-[Test Ended] 

The result is the following Visual: 

How do I create a cumulative total of Test Active? 

The result I'm looking for should be 2012: 250, 2013: 8999; 2014: 37707... with 2019 showing 179671. 

Thanks in advance... I'm a beginner with PowerBI. 

[v-juanli-msft]

Hi @Alienvolm 

You could follow this guide.

https://forum.enterprisedna.co/t/cumulative-sum-of-a-measure-that-creates-another-cumulative-sum/739...]

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @Alienvolm ,

Try either of these solutions

1. DAX measure

Measure 12 = Calculate(sum(Sheet3[Test Active]),filter(All(Sheet3),Sheet3[Year]<=MAX(Sheet3[Year])))

If you need to do it by using column

2. calculated Column:

Column = Calculate(sum(Sheet3[Test Active]),All(Sheet3),Sheet3[Year]<=EARLIER('Sheet3'[Year]))

Let me know for any question.

Thanks,

Tejaswi

[Alienvolm]

Hi Tejaswi, 

I tried them all, and in the end I created a column that equals Test Active Seats (not elegant, but it worked!) and I created a measure with the formula you gave me: 

---

@Anonymous wrote:

Hi @Alienvolm ,

Try either of these solutions

1. DAX measure

Measure 12 = Calculate(sum(Sheet3[Test Active]),filter(All(Sheet3),Sheet3[Year]<=MAX(Sheet3[Year])))

That works! 

Thank you very much for your help! 

Alienvolm

[Alienvolm]

Hi @Anonymous, 

I cannot use the two expressions you suggested because Test Active is not a column, it's a measure. Therefore, this is not a valid argument for SUM. Only Quantity is a Column. 

I tried to create calculated columns instead of measures but it doesn't work. I end up with errors of creating a circular function or I run out of memory and I cannot complete.

Any idea of how I can do the same with measures?

Thanks! 

[Anonymous]

Hi @Alienvolm ,

Can you give a try for this formula?

I haven't tested but you can give a shot:

Cumulative =
VAR CurrentRow = [year]
RETURN
    SUMX (
        FILTER ( ALLSELECTED ( all(sheet3 ), [year] <= CurrentRow ),
        [test active]
    )

Thanks,

Tejaswi

[Alienvolm]

I tried in two different ways... I wasn't able to pick any Date as a Measure, so these are Columns. 

[Anonymous]

Hi @Alienvolm ,

can you try now:

Cumulative = 
VAR CurrentRow = [teset active]
RETURN
    SUMX (
        FILTER ( ALL('Sheet3 (2)'[Service Start Date]),[teset active]<= CurrentRow),
        [teset active] )

If this doesnt work then give me your sample data in an ecxel spredsheet for me to test and see.

Thanks,

Tejaswi