Table with actual status and how to go back in time with a lifecycle change table

[Sander_NL]

Good day, I have two tables:

A table with all apps, controls, status fields. This is a table which is refreshed everyday with the status of current day. In a second table I have status changes with a date of change (multiple time a day is possible even on seconds on the same day). When there is no update then the value of the first table (most recent value is applicable). 

I need to be able to go back in time with a date slicer. I did already experiment with a date table but not having success. The tables have a related based on a key app | control. Below some example data including the expected outcome. 

Can anyone assist?

Table all controls (values as of today e.g. 31-1-2024)  which are daily refreshed)
Key	App	Control	Status
AABS1	AAB	S1	9
AABS2	AAB	S2	8
CAHS1	CAH	S1	7
CAHS2	CAH	S2	9
CAHS3	CAH	S3	1
JKLS1	JKL	S1	4
Table lifecycle (only when a status changes there is a entry)
Key	App	Control	Old status	New status	DateUpdated
AABS1	AAB	S1	6	9	15-01-2024 13:00
AABS2	AAB	S2	5	8	15-01-2024 14:00
CAHS2	CAH	S2	9	1	21-01-2024 11:00
CAHS2	CAH	S2	1	9	21-01-2024 14:00
Examples of output
Slicer on  31-01-2024
Key	App	Control	Status
AABS1	AAB	S1	9
AABS2	AAB	S2	8
CAHS1	CAH	S1	7
CAHS2	CAH	S2	9
CAHS3	CAH	S3	1
JKLS1	JKL	S1	4
Slicer on 14-01-2024
Key	App	Control	Status
AABS1	AAB	S1	6
AABS2	AAB	S2	5
CAHS1	CAH	S1	7
CAHS2	CAH	S2	6
CAHS3	CAH	S3	1
JKLS1	JKL	S1	4

[Anonymous · Accepted solution]

Hi @Sander_NL ,

First of all, I want to ask why it is 6 here:

None of the records in the sample data you provided have CAHS2 = 6.

I suggest you to merge the two tables as a new one in Power Query:

Click "Close and Apply":

I add a calendar table for slicer:

And here is the relationship view:

Then use this DAX to create a measure:

Status_Dynamic = 
IF(
    MAX('Merge_PowerQuery'[DateUpdated]) = BLANK(),
    MAX('Merge_PowerQuery'[Status]),
    IF(
        MAX('Merge_PowerQuery'[DateUpdated]) >= SELECTEDVALUE('Calendar'[Date]),
        MAX('Merge_PowerQuery'[Old status]),
        MAX('Merge_PowerQuery'[Status])
    )
)

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.