Cumulative sums - identifying when a subset of the dataset breaches a certain limit

[jthomson]

Hi,

Have the following data set:

CaseNumber	ValueChange	TransactionDate	CostLimit
1	100	01/01/2021	500
1	200	02/01/2021	500
2	100	03/01/2021	1000
3	200	04/01/2021	1000
3	100	05/01/2021	1000
1	500	06/01/2021	500
2	1000	07/01/2021	1000
4	100	08/01/2021	500
5	250	09/01/2021	1000
1	-400	10/01/2021	500
2	-500	11/01/2021	1000
3	200	12/01/2021	1000
6	100	13/01/2021	1000
7	250	14/01/2021	500
8	420	15/01/2021	500
9	69	16/01/2021	1000
10	400	17/01/2021	1000
4	600	18/01/2021	500
5	100	19/01/2021	1000
11	50	20/01/2021	500

I've got an overall cumulative sum working perfectly on this using a date table related to the TransactionDate column, and a pretty standard measure:

CumulativeSum = calculate(sum([ValueChange]),filter(all(DateTable[Date]),DateTable[Date]<=max(DateTable[Date])))

What I need to do is identify when and by how much, for each case number, the sum is exceeding the CostLimit value, which remains constant for each case number. In other words, it would return a result of 300 for case 1 from 6/1 until 10/1, return a result of 100 for case 2 from 7/1 to 11/1, and return a result of 200 for case 4 from 18/1 onwards - every other case would return a zero or null result. Any ideas?

[v-robertq-msft · Accepted solution]

Hi, @jthomson 

According to your description and explanation with examples, I can roughly understand your requirement, you want to get the Cumulative Sum of [value change] group by [Case Number] and give them the last value which is less than [CostLimit] when they are over the value of [CostLimit], right?

If so, you can try my steps:

I create these calculated columns, I used calculated columns to achieve this because measures are not suitable for the calculation based on the last rows.

CumulativeSum =
CALCULATE(
SUM('Table'[ValueChange]),
FILTER(ALLSELECTED('Table'),
[CaseNumber]=EARLIER([CaseNumber])&&
[TransactionDate]<=EARLIER([TransactionDate])))

Index =

RANKX(FILTER(ALLSELECTED('Table'),[CaseNumber]=EARLIER('Table'[CaseNumber])),[TransactionDate],,ASC,Dense)

Output =
var _lastindex=
CALCULATE(
MAX('Table'[Index]),
FILTER(ALL('Table'),
[CaseNumber]=EARLIER([CaseNumber])&&
[CumulativeSum]<=EARLIER([CostLimit])&&
[Index]<=EARLIER([Index])))
var _lastvalue=
CALCULATE(
MAX('Table'[CumulativeSum]),
FILTER(ALL('Table'),
[CaseNumber]=EARLIER([CaseNumber])&&
[Index]=_lastindex))
return
IF([CumulativeSum]>[CostLimit],_lastvalue,0)

This is the output of these calculated columns in the table:

Then I created a table chart and place it like this:

And I guess this can be what you want.

You can download my test pbix file here

If this result is not what you want, you can explain your logic in detail and share your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jthomson]

Hi,

That seems closer, might take a bit of time to validate what it's showing (not sure it's entirely working as intended given that, for example, entry 4 should be showing 200 after the cumulative sum reaches 700, and it's showing 100), but it looks promising. That said, I'm testing out a summary table instead, which I should be able to get working a bit easier as it'd just be a case of making one relationship and subtracting the related value. I'll make a note of what you've done and come back to it if needed, cheers

[v-robertq-msft]

Hi, @jthomson 

OK, just reply to me if you still have a problem.

Thanks in advance!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jthomson]

I've tried that, and it's not working as intended at all, having tried it on a dataset where the CostLimit is the same for every case and it's never exceeded once - it just takes away the cost limit, doing a similar thing as in this post of Greg's:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

I don't want it to just work out the cumulative sum for the entire data set and then take away the highest value of the cost limit, which is what the measure you've suggested does. That said, I can't just directly port in anything Greg's suggested (e.g. just use SUMX), as I need the calculation to run on a case-by-case basis and not on a line-by-line basis (and respect the date filters that the cumulative sum uses at the same time)

[parry2k]

@jthomson I think we are making it super complicated, but in case you want to return 0 when it is negative, you can wrap your measure around MIN function

Diff = 
VAR _diff = MAX ( Table[Cost Limit] ) - [Your Cummulative Measure]
RETURN
MIN ( _diff, 0 )

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[jthomson]

---

@parry2k wrote:

@jthomson try following measure:

Difference Cost Limit = 
MAX ( Table[Cost Limit] ) - [Your Cummulative Measure]

I think you meant to word that as [my cumulative measure] - max(table[Cost Limit]), but in any case I'm not sure that is going to work, probably falling into a "why is my totals row not working as I want it to" issue. Might well be the way I've phrased the question in fairness. If I simplify the data and it's like this:

CaseNumber	ValueChange	TransactionDate	CostLimit
1	2000	22/02/2021	1000
2	2000	22/02/2021	1000
3	2000	22/02/2021	1000
4	500	22/02/2021	1000

When looking at the data as a whole, I need it to return 3000 - I'm thinking the measure you provide would work out that my cumulative measure is showing 6500, but then only subtract 1000. Similarly, if cases 1, 2 and 3 in that above table had ValueChange of just 500, I'd need the end result to be 0, not -1000.

[parry2k]

@jthomson try following measure:

Difference Cost Limit = 
MAX ( Table[Cost Limit] ) - [Your Cummulative Measure]

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡