Max Value to Date by Date

[marcritchie]

I have a table with weekly snapshot data:

The ActualLaborUnits value should be cumulative but due to the way this data is collected some weeks the ActualLaborUnits is lower than the previous week.

What I want to do each week is show the Max value of ActualLaborUnits up to that date e.g. in the above example the value for Reporting Date 01/05/2022 (line 71) should be 714.0975 as that is higher than the value for that week.

When it reaches Reporting Date 05/06/2022 then the value would be 827.1523 as that would then be the new Max cumulative value.

I have tried indexing and getting previous row but this runs into problems due to there being multiple combinations of jobId, Element, Discipline etc. 

Is there a method in Power Query I can use to get the max ActualLaborUnits to date by date?

[v-kkf-msft · Accepted solution]

Hi @marcritchie ,

Please try the custom column.

= let id = [Jobld], Rdate = [Reporting Date]
in List.Max( Table.SelectRows(#"Changed Type", each [Jobld] = id and [Reporting Date] <= Rdate)[ActualLaborUnits])

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vijay_A_Verma]

I need to know the definition of week for example why 24/04 and 01/05 should be part of a single week? What about the other weeks?