DAX total by last status

Question

My datasouce looks like following:

Case_ID;Date;Status_ID;Amount
1;01.09.2021;0;300
1;05.09.2021;2;320
1;06.09.2021;3;320
2;05.09.2021;3;100
3;02.09.2021;0;200
3;07.09.2021;1;200
...

The logic is that for a particular businness case the Status_ID and the Amount is valid from the "Date" until a new change or until the end of the calendar.

For the above data and end of calendar as of 8.9.2021 my required measure should give following output.

Date       Case_ID  Status_ID     Amount
1.9.2021      1         0           300
2.9.2021      1         0           300
3.9.2021      1         0           300
4.9.2021      1         0           300
5.9.2021      1         2           320
6.9.2021      1         3           320
7.9.2021      1         3           320 
8.9.2021      1         3           320 
5.9.2021      2         3           100
6.9.2021      2         3           100
7.9.2021      2         3           100
8.9.2021      2         3           100
2.9.2021      3         0           200
3.9.2021      3         0           200 
4.9.2021      3         0           200 
5.9.2021      3         0           200 
6.9.2021      3         0           200 
7.9.2021      3         1           200 
8.9.2021      3         1           200

How can I construct such a measure with DAX?

I have found several blogs on "Semiadditive measures" with several approaches how to get the last amount for the Case_ID but I do not know how to handle the changing status.

user_3652345 · Answer

If Power Query Editor create a new table using the below code-

Note: Replace "your_table_name" with your original table name from the whole script.

let
    Source = Table.SelectColumns(your_table_name, {"Case_ID", "Date"}),
    #"Grouped Rows" = Table.Group(Source, {"Case_ID"}, {{"min_date", each List.Min([Date]), type nullable date}, {"max_date", each List.Max([Date]), type nullable date}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each ([max_date]-[min_date])),
    #"Extracted Days" = Table.TransformColumns(#"Added Custom",{{"Custom", Duration.Days, Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Days", "Custom.1", each List.Range({0..[Custom]},0)),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Custom.2", each Date.AddDays([min_date],[Custom.1])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"min_date", "max_date", "Custom", "Custom.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.2", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Case_ID", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"Case_ID", "Date"}, your_table_name, {"Case_ID", "Date"}, "your_table_name", JoinKind.LeftOuter),
    #"Expanded your_table_name" = Table.ExpandTableColumn(#"Merged Queries", "your_table_name", {"Status_ID", "Amount"}, {"your_table_name.Status_ID", "your_table_name.Amount"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded your_table_name",{{"your_table_name.Status_ID", "Status_ID"}, {"your_table_name.Amount", "Amount"}}),
    #"Filled Down" = Table.FillDown(#"Renamed Columns1",{"Status_ID", "Amount"})
in
    #"Filled Down"

Here is the output-

user_7108589 · Answer

Since you were lookong for a simple DAX solution:

Last Amount = 
VAR current_date = 
    MAX('Calendar'[Date])
VAR last_date =
    CALCULATE(
        MAX('Table'[Date]),
        'Calendar'[Date] <= current_date
    )
RETURN
    CALCULATE(
        MAX('Table'[Amount]),
        'Calendar'[Date] = last_date
    )

In a table visual, ordered by Case_ID, this looks like

DAX total by last status

2 Answers