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
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.
Solved! Go to 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
Hi @Alienvolm
You could follow this guide.
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.
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!
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!
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.
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |