Forum Discussion
Dax calc previous & current value
Hi all,
I need help with some calculations. I have the following data and the desired result:
Date | Task1 | Task2 | Task3 | Task4(manually entered) |
01/04/2021 | 1000 | 500 | 10 000 | 10 000 |
02/04/2021 | 2000 | 300 | 11 700 |
|
03/04/2021 | 2500 | 500 | 13 700 |
|
04/04/2021 | 300 | 300 | 20 000 | 20 000 |
05/04/2021 | 500 | 400 | 20 100 |
|
The first three columns (date, task1, task2) are coming from a measure table. Task4 is a column, which have manually entered values from time to time).
The formula I need is for Task3. Task3 first value should be 10000 (the same as the first row in Task4). In the next rows,Task3 should calculate (the first Task3 value + Task1 from the second row) – Task2 from the second row. For 3rd of April task3 should calculate the (Task3 from row 2 + task1 from row3) – task2 from row 3. If there is a value in Task4, task 3 should take it, if there is no value in Task4, the formula for task3 should be (task3 from previous row + task1 for the current row – task2 from the current row).
Thank you.
// Tasks let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY3BDcAwCAN34R0pxsA0UfZfo2moVH+M0J1hLYNP5CToNswBnFE33wW2x3EoDtuJm41DccmJxik4pMz/QYnS1fwO7Ac=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Task1 = _t, Task2 = _t, Task4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Task1", Int64.Type}, {"Task2", Int64.Type}, {"Task4", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Date", "Task1", "Task2", "Task4"}), #"Invoked Custom Function" = Table.AddColumn(#"Reordered Columns", "fnGetCumulativeSum", each fnGetCumulativeSum(fnGetSummationStartIndex([Index]), [Index])), #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Index"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"fnGetCumulativeSum", "Task3"}}), #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns",{"Date", "Task1", "Task2", "Task3", "Task4"}) in #"Reordered Columns1" // Summation let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY3BDcAwCAN34R0pxsA0UfZfo2moVH+M0J1hLYNP5CToNswBnFE33wW2x3EoDtuJm41DccmJxik4pMz/QYnS1fwO7Ac=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Task1 = _t, Task2 = _t, Task4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Task1", Int64.Type}, {"Task2", Int64.Type}, {"Task4", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Date", "Task1", "Task2", "Task4"}), #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Date"}), #"Added Custom" = Table.AddColumn(#"Removed Columns1", "Task1 - Task2", each [Task1] - [Task2]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Helper2", each if [Task4] is null then [#"Task1 - Task2"] else [Task4]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Task1 - Task2", "Task1", "Task2"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Helper2", "Value"}}) in #"Renamed Columns" // fnGetCumulativeSum (StartIndex as number, EndIndex as number) as number => let CumulativeSum = List.Sum( Table.SelectRows( Summation, each StartIndex <= [Index] and [Index] <= EndIndex )[Value] ) in CumulativeSum // fnGetSummationStartIndex (CurrentIndex as number) as number => let SummationStartIndex = List.Max( Table.SelectRows( Summation, each [Index] <= CurrentIndex and [Task4] <> null )[Index] ) in SummationStartIndexJust copy the code as it is above and paste into this pane (right-click the pane and paste):
I have assumed that the earliest row in the Task table will have a non-null manual entry (Task 4). If not, then you'll have to modify the functions so that they return what you want.
2 Replies
// Tasks let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY3BDcAwCAN34R0pxsA0UfZfo2moVH+M0J1hLYNP5CToNswBnFE33wW2x3EoDtuJm41DccmJxik4pMz/QYnS1fwO7Ac=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Task1 = _t, Task2 = _t, Task4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Task1", Int64.Type}, {"Task2", Int64.Type}, {"Task4", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Date", "Task1", "Task2", "Task4"}), #"Invoked Custom Function" = Table.AddColumn(#"Reordered Columns", "fnGetCumulativeSum", each fnGetCumulativeSum(fnGetSummationStartIndex([Index]), [Index])), #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Index"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"fnGetCumulativeSum", "Task3"}}), #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns",{"Date", "Task1", "Task2", "Task3", "Task4"}) in #"Reordered Columns1" // Summation let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY3BDcAwCAN34R0pxsA0UfZfo2moVH+M0J1hLYNP5CToNswBnFE33wW2x3EoDtuJm41DccmJxik4pMz/QYnS1fwO7Ac=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Task1 = _t, Task2 = _t, Task4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Task1", Int64.Type}, {"Task2", Int64.Type}, {"Task4", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Date", "Task1", "Task2", "Task4"}), #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Date"}), #"Added Custom" = Table.AddColumn(#"Removed Columns1", "Task1 - Task2", each [Task1] - [Task2]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Helper2", each if [Task4] is null then [#"Task1 - Task2"] else [Task4]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Task1 - Task2", "Task1", "Task2"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Helper2", "Value"}}) in #"Renamed Columns" // fnGetCumulativeSum (StartIndex as number, EndIndex as number) as number => let CumulativeSum = List.Sum( Table.SelectRows( Summation, each StartIndex <= [Index] and [Index] <= EndIndex )[Value] ) in CumulativeSum // fnGetSummationStartIndex (CurrentIndex as number) as number => let SummationStartIndex = List.Max( Table.SelectRows( Summation, each [Index] <= CurrentIndex and [Task4] <> null )[Index] ) in SummationStartIndexJust copy the code as it is above and paste into this pane (right-click the pane and paste):
I have assumed that the earliest row in the Task table will have a non-null manual entry (Task 4). If not, then you'll have to modify the functions so that they return what you want.