Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
66 | |
49 |
User | Count |
---|---|
140 | |
113 | |
104 | |
64 | |
60 |