Forum Discussion

Mp1977's avatar
Mp1977
Helper II
5 years ago

Cumulative Max

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 

DatesValuesCumulative TotalCumulative Max 
08/06/2020-111-111-111
09/06/2020-75-186-111
10/06/202019488
15/06/2020-188-1808
16/06/202018778
18/06/202071414
19/06/2020299313313
22/06/202041354354
23/06/202043397397
24/06/2020197594594
25/06/2020297891891
26/06/202026411551155
29/06/2020-2011351155
30/06/2020611411155
01/07/2020-1749671155
02/07/202028212491249
03/07/202011813671367
06/07/2020-2913381367
07/07/2020-3213061367
08/07/20205413601367
09/07/202040317631763
10/07/202024320062006
13/07/20203720432043
15/07/2020-17318702043
16/07/202063725072507
20/07/20202525322532
21/07/2020-9424382532
22/07/2020-27221662532
23/07/202035325192532
Need Help

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

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

    • Mp1977's avatar
      Mp1977
      Helper II
      5 years ago

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

       

      Thank you 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Mp1977
      Helper II
      5 years ago

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