M Function - Advanced Running Total logic

[kramaswamy]

Hello,

Let me preface this by saying I'm a complete newb as far as M is concerned. I am a fairly experienced developer generally speaking, though. Any assistance would be greatly appreciated.

I'm using the following query to generate a running total:

(MyTable as table, MyColumn as text, MyNewColumn as text) as table =>
let
    Source = Table.Buffer(MyTable),
    TableType = Value.Type(Table.AddColumn(Source, MyNewColumn, each null, type duration)),
    Cumulative = List.Skip(List.Accumulate(Table.Column(Source, MyColumn),{0},(cumulative,current) => cumulative & {List.Last(cumulative) + Duration.TotalMinutes(Duration.From(current))})),
    AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
    AddedRunningSum

I have a few questions. Firstly, I'd like to adjust the above query to give me two running totals instead of just one - the first being per day, and the second being per week. Right now, I'm doing this by running the query twice, which I'm sure is inefficient.

Secondly, I need to adjust that query to apply some more selective logic. Right now I'm just taking the running total, but what I'd like to do is also split that total into three columns - the running total capped at X, the remainder total capped at Y, and the final total capped at Z. This is being used to calculate overtime values. The X and Y values above (call them RegCutoff and OT1Cutoff) would be inputs to the function. As an example, suppose RegCutoff is 480 and OT1Cutoff is 240. If I have a single record for the day at 780 minutes (13 hours), I want three columns with the first containing 480, the second containing 240, and the third containing 60. As another example, if my single record is 540 minutes (9 hours), then my three columns would be 480, 60, and 0. And as a third example, if I have two records for the day, both with 300 minutes (5 hours), then what I'd want would be for the first row to have 300, 0, and 0, and the second row to have 180, 300, 0.  

Thirdly, as witnessed in the dataset below, my weekly total isn't working properly when there are two records on the same day:

Ignore the above thirdly - must have been tired when I wrote it, the weekly running total is fine.

user_id	date	shift_duration	daily_running_sum	weekly_running_sum
1	2021-06-21	0.08:30:00	510	510
1	2021-06-22	0.08:30:00	510	1020
1	2021-06-23	0.08:30:00	510	1530
1	2021-06-24	0.08:30:00	510	2040
1	2021-06-25	0.08:30:00	510	2550
2	2021-06-21	0.08:00:00	480	480
2	2021-06-22	0.08:00:00	480	960
2	2021-06-23	0.08:00:00	480	1440
2	2021-06-24	0.08:00:00	480	1920
2	2021-06-25	0.08:00:00	480	2400
2	2021-06-27	0.07:00:00	420	2820
3	2021-06-21	0.06:00:00	360	360
3	2021-06-21	0.07:30:00	810	810
3	2021-06-22	0.08:30:00	510	1320
3	2021-06-23	0.08:30:00	510	1830
3	2021-06-24	0.08:30:00	510	2340
3	2021-06-25	0.08:30:00	510	2850
3	2021-06-26	0.04:00:00	240	3090
3	2021-06-27	0.04:00:00	240	3330
3	2021-06-27	0.06:00:00	600	3690

[mahoneypat]

It is possible to do this in M/query, but it is done much more easily as a DAX measure.  That way, you are not storing an extra column of data too.  It is good that you have learned enough M to do the above, but DAX is the way to go for these kind of calculations.

Cumulative total – DAX Patterns

Pat

[kramaswamy]

So as indicated above, the third problem was just my poor reading of my own data. The second problem I've basically also resolved - I can just add new columns with conditional logic based off of the daily_running_sum and weekly_running_sum columns.

I would still like to know if there is a way to combine those two into a single function, though, instead of having to call the function twice - I'm guessing on larger data sets that'll end up being an expensive query to iterate through everything twice.