How to calculate max amount so far in a table.

[AmyJan25]

Hello, 

I have a table with "Date" and "Daily Revenue" and I looking to create another column for "Max Daily Revenue to Date" but having trouble making this work.

Any insights would be appreciated!!!

Date	Daily Revenue	Max Daily Revenue to Date
16-Apr-25	882	1,000
15-Apr-25	973	1,000
14-Apr-25	994	1,000
13-Apr-25	1,000	1,000
12-Apr-25	964	998
11-Apr-25	942	998
10-Apr-25	865	998
09-Apr-25	875	998
08-Apr-25	926	998
07-Apr-25	975	998
06-Apr-25	986	998
05-Apr-25	975	998
04-Apr-25	998	998
03-Apr-25	925	980
02-Apr-25	963	980
01-Apr-25	980	980

[SRA]

Hi,

Could this fits to your requirement ?

RangeMax(Above(TOTAL Sum(DailyRevenue), 0, RowNo(TOTAL)))

Sorting date ascending you will get :

Regards

[AmyJan25]

It does help in a straight table, but ideally it would be helpful to build this logic into the load script so that I can build other rules off this amount etc. if you know how this could work?

[SRA]

You have a table agreagated by Date ? If yes, here is one example :

Source:

Load * InLine

[Date,DailyRevenue

16-04-2025, 882

15-04-2025, 973

14-04-2025, 994

13-04-2025, 1000

12-04-2025, 964

11-04-2025, 942

10-04-2025, 865

09-04-2025, 875

08-04-2025, 926

07-04-2025, 975

06-04-2025, 986

05-04-2025, 975

04-04-2025, 998

03-04-2025, 925

02-04-2025, 963

01-04-2025, 980

];

Data:

Load

    Date(Date#(Date,'DD-MM-YYYY')) as Date,

    DailyRevenue,

    RangeMax(DailyRevenue, Peek('MaxToDate')) as MaxToDate

Resident Source

Order By Date;

Drop Table Source;

[AmyJan25 · Accepted solution]

Found a solution by doing the below in the load script:

If(RowNo()=1, DailyRevenue, RangeMax(DailyRevenue, Peek('MaxPrice'))) as MaxPrice