Retrieving the date when a value changes

[Anonymous]

Hello, 

I have 2 tables : 

History (weekly imported)

Name	Value	DATE
Name 1	TRUE	26/01/2025
Name 2	TRUE	26/01/2025
Name 3	TRUE	26/01/2025
Name 4	FALSE	26/01/2025
Name 5	FALSE	26/01/2025
Name 6	FALSE	26/01/2025
Name 7	FALSE	26/01/2025
Name 8	FALSE	26/01/2025
Name 9	FALSE	26/01/2025
Name 10	FALSE	26/01/2025
Name 1	TRUE	19/01/2025
Name 2	FALSE	19/01/2025
Name 3	FALSE	19/01/2025
Name 4	FALSE	19/01/2025
Name 5	FALSE	19/01/2025
Name 6	TRUE	19/01/2025
Name 7	FALSE	19/01/2025
Name 8	FALSE	19/01/2025
Name 9	FALSE	19/01/2025
Name 10	FALSE	19/01/2025
Name 1	FALSE	12/01/2025
Name 2	FALSE	12/01/2025
Name 3	FALSE	12/01/2025
Name 4	FALSE	12/01/2025
Name 5	FALSE	12/01/2025
Name 6	FALSE	12/01/2025
Name 7	FALSE	12/01/2025
Name 8	FALSE	12/01/2025
Name 9	FALSE	12/01/2025
Name 10	FALSE	12/01/2025

And Current (Daily imported)

Name	Value	DATE
Name 1	TRUE	27/01/2025
Name 2	TRUE	27/01/2025
Name 3	TRUE	27/01/2025
Name 4	FALSE	27/01/2025
Name 5	FALSE	27/01/2025
Name 6	FALSE	27/01/2025
Name 7	FALSE	27/01/2025
Name 8	FALSE	27/01/2025
Name 9	FALSE	27/01/2025
Name 10	FALSE	27/01/2025

I would like to add the the table Current (Daily import) the date the Value changed from FALSE  >>> TRUE. Then calculate how many date the Name in value TRUE.

Result would be like this :

Table History

Name	Value	DATE	VALUE Change date
Name 1	TRUE	26/01/2025
Name 2	TRUE	26/01/2025	26/01/2025
Name 3	TRUE	26/01/2025	26/01/2025
Name 4	FALSE	26/01/2025
Name 5	FALSE	26/01/2025
Name 6	FALSE	26/01/2025
Name 7	FALSE	26/01/2025
Name 8	FALSE	26/01/2025
Name 9	FALSE	26/01/2025
Name 10	FALSE	26/01/2025
Name 1	TRUE	19/01/2025	19/01/2025
Name 2	FALSE	19/01/2025
Name 3	FALSE	19/01/2025
Name 4	FALSE	19/01/2025
Name 5	FALSE	19/01/2025
Name 6	TRUE	19/01/2025	19/01/2025
Name 7	FALSE	19/01/2025
Name 8	FALSE	19/01/2025
Name 9	FALSE	19/01/2025
Name 10	FALSE	19/01/2025
Name 1	FALSE	12/01/2025
Name 2	FALSE	12/01/2025
Name 3	FALSE	12/01/2025
Name 4	FALSE	12/01/2025
Name 5	FALSE	12/01/2025
Name 6	FALSE	12/01/2025
Name 7	FALSE	12/01/2025
Name 8	FALSE	12/01/2025
Name 9	FALSE	12/01/2025
Name 10	FALSE	12/01/2025

Table Current

Name	Value	DATE	Date Changed
Name 1	TRUE	27/01/2025	19/01/2025
Name 2	TRUE	27/01/2025	26/01/2025
Name 3	TRUE	27/01/2025	26/01/2025
Name 4	FALSE	27/01/2025
Name 5	FALSE	27/01/2025
Name 6	TRUE	27/01/2025	19/01/2025
Name 7	FALSE	27/01/2025
Name 8	FALSE	27/01/2025
Name 9	FALSE	27/01/2025
Name 10	FALSE	27/01/2025

Could you please help? Thank you.

Tg

[Irwan · Accepted solution]

hello @Anonymous 

please check if this accomodate your need (i assumed you want to do this with calculated column).

1. create a calculated column in 'History' table with following DAX.

Value Change Date = 
var _PreviousDate = 
MAXX(
    FILTER(
        'History',
        'History'[DATE ]<EARLIER('History'[DATE ])&&
        'History'[Name ]=EARLIER('History'[Name ])&&
        'History'[Value]<>EARLIER('History'[Value])
    ),
    'History'[DATE ]
)
var _LastWeek = 
IF(
    _PreviousDate>'History'[DATE ]-8,
    _PreviousDate
)
var _TrueValue = 
IF(
    'History'[Value]="True",
    _LastWeek
)
Return
IF(
    not ISBLANK(_TrueValue),
    'History'[DATE ]
)

2. create a calculated column in 'Current' table with following DAX

Date Changed =
MAXX(
    FILTER(
        'History',
        'Current'[Name ]='History'[Name ]
    ),
    'History'[Value Change Date]
)

Hope this will help.

Thank you.

[Anonymous]

Thank you for your help.

[Irwan]

Hello @Anonymous 

Glad to be a help.

Thank you.