Cumulative Max

[Mp1977]

HI ! 

I have a table with date and value column. I would like to calculate the cumulative total value, which I got, but also the cumulative max of the cumulative total value.

I tried to use Maxx but performance is terrible wtith a large table. I tried to use a virtual table, but max does not accpet a column from it

Is there a better alternative?


My formulas:

Cumulative Total = 

 CALCULATE (

Sum('tab'[Values];

FILTER (

ALLSELECTED( 'tab' );

'tab'[Dates]<= MAX ( 'tab'[Dates] )

)

)

Cumulative Max of Cumulative Total =

CALCULATE (

MAXx(

FILTER (

ALLSELECTED( 'tab' );

'tab'[Dates]<= MAX ( 'tab'[Dates] )

); [Cumulative Total ]

))

Table 

Dates	Values	Cumulative Total	Cumulative Max
08/06/2020	-111	-111	-111
09/06/2020	-75	-186	-111
10/06/2020	194	8	8
15/06/2020	-188	-180	8
16/06/2020	187	7	8
18/06/2020	7	14	14
19/06/2020	299	313	313
22/06/2020	41	354	354
23/06/2020	43	397	397
24/06/2020	197	594	594
25/06/2020	297	891	891
26/06/2020	264	1155	1155
29/06/2020	-20	1135	1155
30/06/2020	6	1141	1155
01/07/2020	-174	967	1155
02/07/2020	282	1249	1249
03/07/2020	118	1367	1367
06/07/2020	-29	1338	1367
07/07/2020	-32	1306	1367
08/07/2020	54	1360	1367
09/07/2020	403	1763	1763
10/07/2020	243	2006	2006
13/07/2020	37	2043	2043
15/07/2020	-173	1870	2043
16/07/2020	637	2507	2507
20/07/2020	25	2532	2532
21/07/2020	-94	2438	2532
22/07/2020	-272	2166	2532
23/07/2020	353	2519	2532

[Anonymous]

@Mp1977 

Just try calculate(max()...

Cum max = CALCULATE (
MAX('Table'[Values]),
FILTER (
ALLSELECTED( 'Table' ),
'Table'[Dates]<= MAX ( 'Table'[Dates] )))

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mp1977]

Thank you for your reply, but Max does not accept acolumn form a virtual table as parameter.

[lbendlin]

is your computation depending on user filter choices or can it be done as a calculated column?

[Mp1977]

Hi! It requires user to filter date and a group  of stock

Thank you