Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
kramaswamy
Frequent Visitor

M Function - Advanced Running Total logic

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_iddateshift_durationdaily_running_sumweekly_running_sum
12021-06-210.08:30:00510510
12021-06-220.08:30:005101020
12021-06-230.08:30:005101530
12021-06-240.08:30:005102040
12021-06-250.08:30:005102550
22021-06-210.08:00:00480480
22021-06-220.08:00:00480960
22021-06-230.08:00:004801440
22021-06-240.08:00:004801920
22021-06-250.08:00:004802400
22021-06-270.07:00:004202820
32021-06-210.06:00:00360360
32021-06-210.07:30:00810810
32021-06-220.08:30:005101320
32021-06-230.08:30:005101830
32021-06-240.08:30:005102340
32021-06-250.08:30:005102850
32021-06-260.04:00:002403090
32021-06-270.04:00:002403330
32021-06-270.06:00:006003690
2 REPLIES 2
mahoneypat
Microsoft Employee
Microsoft Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


kramaswamy
Frequent Visitor

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.