Forum Discussion
Rolling Total with a twist
- 3 years ago
The DAX calculation involves recursion, which is too hard to be (almost) impossible for most DAX users, me included. In comparison, Power Query solution is way more feasible as shown above.
The DAX calculation involves recursion, which is too hard to be (almost) impossible for most DAX users, me included. In comparison, Power Query solution is way more feasible as shown above.
Worked perfectly. I hadn't even considered a Power Query solution and as such didn't initially recogonize the code. Thank You! Jim
- JImAyers3 years ago
Helper I
Dear Sir: One important fact I left out last weekend was the inclusion of Employee ID; thought it not important, but it is. Table with employee id is shown below. I added my index to be grouped by employee ID as the M Code below shows. So each employee starts off with an index of 1 and increments from there. RT is the recursive summation that I emulated from your previous code but it is off from what it should be. I've eaten so much humble pie on this, I'm no longer belittled by going to the well once again. Thanking in advance for any help with tweeking this recursive Running Total.
let
Source = Csv.Document(File.Contents("C:\AyersDOCS\data1.csv"),[Delimiter=",", Columns=5, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Employee", Int64.Type}, {"BedDate", type datetime}, {"Days Used", Int64.Type}, {"", type text}, {"Shuld Be", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"BedDate", "Date"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"Employee"}, {{"Count", each _, type table [Employee=nullable number, Date=nullable datetime, Days Used=nullable number, #""=nullable text, Shuld Be=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Index", each Table.AddIndexColumn([Count],"Index",1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Index"}),
#"Expanded Index" = Table.ExpandTableColumn(#"Removed Other Columns", "Index", {"Employee", "Date", "Days Used", "", "Shuld Be", "Index"}, {"Index.Employee", "Index.Date", "Index.Days Used", "Index.", "Index.Shuld Be", "Index.Index"}),
#"Running Total" = Table.RemoveColumns(Table.AddColumn(#"Expanded Index", "RT", each List.Accumulate(Table.ToRecords(Table.MinN(#"Expanded Index","Index.Index",[Index.Index])), 0, (s,c)=> List.Min({s+c[Index.Days Used],5}))), "Index.Index")
in
#"Running Total"Employee Date Days Used Should Be RT 36746 3/1/2022 0:00 0 0 0 36746 4/1/2022 0:00 1 1 0 36746 5/1/2022 0:00 0 1 1 36746 6/1/2022 0:00 1 2 1 36746 7/1/2022 0:00 -1 1 1 36746 8/1/2022 0:00 1 2 1 36746 9/1/2022 0:00 1 3 2 28515 3/1/2020 0:00 0 0 0 28515 4/1/2020 0:00 0 0 0 28515 5/1/2020 0:00 0 0 1 28515 6/1/2020 0:00 0 0 1 28515 7/1/2020 0:00 0 0 1 28515 2/1/2021 0:00 0 0 1 28515 3/1/2021 0:00 0 0 2 28515 4/1/2021 0:00 0 0 2 28515 5/1/2021 0:00 0 0 1 28515 6/1/2021 0:00 0 0 1 28515 7/1/2021 0:00 1 1 2 28515 8/1/2021 0:00 1 2 2 28515 9/1/2021 0:00 1 3 3 28515 10/1/2021 0:00 1 4 3 28515 11/1/2021 0:00 1 5 3 28515 12/1/2021 0:00 1 5 3 28515 1/1/2022 0:00 1 5 3 28515 2/1/2022 0:00 1 5 4 28515 3/1/2022 0:00 1 5 5 28515 4/1/2022 0:00 1 5 5 28515 5/1/2022 0:00 1 5 5 28515 6/1/2022 0:00 '-4 1 5 28515 7/1/2022 0:00 1 2 5 28515 8/1/2022 0:00 1 2 5 28515 9/1/2022 0:00 1 4 5