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

[alsm]

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?

[alsm]

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?

[Dangar332]

Hi, @alsm 

Try below for cumsum min

Measure=

Calculate (

     Minx(

        table, [cumsum]),

        Allexcept(table, table[refdtasdate]

)

[alsm]

@Dangar332 , sorry totally lost  😞

What is this?

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

Where can I read about this?

[Dangar332]

Hi, @alsm 

Measure=

Calculate (

     Minx(

        table, [cumsum]),

        Allexcept(table, table[refdtasdate]

))

[Greg_Deckler · Accepted solution]

@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:

CumSumMin = 
    VAR __Table = 'Table'
    VAR __Table1 = 
      ADDCOLUMNS(
        'Table',
        "CumSum", VAR __DispOrder = [DisplayOrder] RETURN SUMX(FILTER(__Table),[DisplayOrder] <= __DispOrder),[Value])
      )
RETURN
    MINX(__Table1,[CumSum])