Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Alienvolm
Helper IV
Helper IV

Cumulative Totals for measures

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: 

Inventory Original.PNG

 

I have added the following Calculated Columns: 

Calculated Columns.PNG

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: 

Inventory Visual.PNG

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. 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

7 REPLIES 7
v-juanli-msft
Community Support
Community Support

Hi @Alienvolm 

You could follow this guide.

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

 

2.png

 

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
Not applicable

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

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! 

 

Cumulative Active Seats.PNG

 

Thank you very much for your help! 

 

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
Not applicable

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

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

 

All.PNG

 

Allselected.PNG

Anonymous
Not applicable

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.