topic Re: Calculate cumulative total for each row of the date and then plot minimum for each date in DAX Commands and Tips

Calculate cumulative total for each row of the date and then plot minimum for each date

alsm — Tue, 09 Jan 2024 17:16:41 GMT

Finding it difficult to explain in words so will try with example.

I have data as shown in black column:

Timeband	Value	DisplayOrder	Entity	RefDtAsDate	CumSum	Min CumSum
0D <= 1D	100	1	E1	04-Jan-24	100	0
1D <= 2D	-50	2	E1	04-Jan-24	50
2D <= 3D	75	3	E1	04-Jan-24	125
3D <= 4D	-100	4	E1	04-Jan-24	25
4D <= 5D	-25	5	E1	04-Jan-24	0

0D <= 1D	150	1	E1	05-Jan-24	150	50
1D <= 2D	25	2	E1	05-Jan-24	175
2D <= 3D	-75	3	E1	05-Jan-24	100
3D <= 4D	-50	4	E1	05-Jan-24	50
4D <= 5D	50	5	E1	05-Jan-24	100

0D <= 1D	200	1	E1	06-Jan-24	200	35
1D <= 2D	-100	2	E1	06-Jan-24	100
2D <= 3D	50	3	E1	06-Jan-24	150
3D <= 4D	35	4	E1	06-Jan-24	185
4D <= 5D	-150	5	E1	06-Jan-24	35

0D <= 1D	20	1	E2	04-Jan-24	20	-80
1D <= 2D	25	2	E2	04-Jan-24	45
2D <= 3D	-75	3	E2	04-Jan-24	-30
3D <= 4D	-50	4	E2	04-Jan-24	-80
4D <= 5D	30	5	E2	04-Jan-24	-50

0D <= 1D	-50	1	E2	05-Jan-24	-50	-85
1D <= 2D	-10	2	E2	05-Jan-24	-60
2D <= 3D	20	3	E2	05-Jan-24	-40
3D <= 4D	-45	4	E2	05-Jan-24	-85
4D <= 5D	25	5	E2	05-Jan-24	-60

0D <= 1D	200	1	E2	06-Jan-24	200	50
1D <= 2D	-100	2	E2	06-Jan-24	100
2D <= 3D	50	3	E2	06-Jan-24	150
3D <= 4D	50	4	E2	06-Jan-24	200
4D <= 5D	-150	5	E2	06-Jan-24	50

For each date there are time buckets with displayorder, I want to

- first do rolling sum of 'value' for each date and time bucket,

- then find the minimum value of rolling sum

- plot this and also show which day was the minimum

so if filter on screen = E1, then char looks like

Min on 4-Jan = "4D <= 5D"

Min on 5-Jan = "3D <= 4D" and so on

and if filter = E2., then chart looks like

Tried lot os combination of summarize and filter but no luck ... any hints?

Re: Calculate cumulative total for each row of the date and then plot minimum for each date

Greg_Deckler — Tue, 09 Jan 2024 17:24:50 GMT

alsm Well, why can't you just do a MIN or MINX? Seems like a lot of extra work otherwise.

You could do something like this:

(1) Better Running Total - Microsoft Fabric Community

Based on this you should be able to do this:

Re: Calculate cumulative total for each row of the date and then plot minimum for each date

alsm — Wed, 10 Jan 2024 09:56:30 GMT

oh wow!

Greg_Deckler Thank you, this was quite an easy solution to understand and implement. I looked at your video too. Very useful, thank you for that too

I have a follow up question.

I also want to show the minimum position

so I implmented additional Measure

CumSumMin = VAR __Table = 'Table' VAR __Table1 = ADDCOLUMNS( 'Table', "CumSum", VAR __DispOrder = [DisplayOrder] RETURN SUMX(FILTER(__Table),[DisplayOrder] <= __DispOrder),[Value]) ) RETURN MINX(__Table1,[CumSum]) --------- CumSumMinPos = VAR __Table = 'Table' VAR __Table1 = ADDCOLUMNS( 'Table', "CumSum", VAR __DispOrder = [DisplayOrder] RETURN SUMX(FILTER(__Table),[DisplayOrder] <= __DispOrder),[Value]) ) VAR _min_val = [CumSumMin] //Previous Measure RETURN CALCULATE(MAX(Table[DisplayOrder]),FILTER(__Table1, [CumSum] = _min_val))

This works fine but see, large part of the measure code of CumSumMinPos is redundant with Measure CumSumMin. Is there a way to write it more efficiently?

Re: Calculate cumulative total for each row of the date and then plot minimum for each date

Dangar332 — Wed, 10 Jan 2024 10:19:46 GMT

Hi, alsm

Try below for cumsum min

Measure=

Calculate (

Minx(

table, [cumsum]),

Allexcept(table, table[refdtasdate]

)

Re: Calculate cumulative total for each row of the date and then plot minimum for each date

alsm — Wed, 10 Jan 2024 10:26:03 GMT

Dangar332 , sorry totally lost 😞

What is this?

Googling: DAX **bleep**, shows a result or two with time intelligence.

Where can I read about this?

Re: Calculate cumulative total for each row of the date and then plot minimum for each date

Dangar332 — Wed, 10 Jan 2024 10:30:28 GMT

Hi, alsm

Measure=

Calculate (

Minx(

table, [cumsum]),

Allexcept(table, table[refdtasdate]

))