Dax calc previous & current value

[Anonymous]

Hi all,

I need help with some calculations. I have the following data and the desired result:

Date	Task1	Task2	Task3 (task3 prev. +task1) – task2	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.

[Anonymous · Accepted solution]

// 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
    SummationStartIndex

Just 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.

[Anonymous]

Power Query is the tool you want to use for this calculation. It'll be easy there. Not so easy in DAX and not so fast, either.