Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Retrieving the date when a value changes

Hello, 

 

I have 2 tables : 

History (weekly imported)

Name ValueDATE 
Name 1 TRUE26/01/2025
Name 2TRUE26/01/2025
Name 3TRUE26/01/2025
Name 4FALSE26/01/2025
Name 5FALSE26/01/2025
Name 6FALSE26/01/2025
Name 7FALSE26/01/2025
Name 8FALSE26/01/2025
Name 9FALSE26/01/2025
Name 10FALSE26/01/2025
Name 1 TRUE19/01/2025
Name 2FALSE19/01/2025
Name 3FALSE19/01/2025
Name 4FALSE19/01/2025
Name 5FALSE19/01/2025
Name 6TRUE19/01/2025
Name 7FALSE19/01/2025
Name 8FALSE19/01/2025
Name 9FALSE19/01/2025
Name 10FALSE19/01/2025
Name 1 FALSE12/01/2025
Name 2FALSE12/01/2025
Name 3FALSE12/01/2025
Name 4FALSE12/01/2025
Name 5FALSE12/01/2025
Name 6FALSE12/01/2025
Name 7FALSE12/01/2025
Name 8FALSE12/01/2025
Name 9FALSE12/01/2025
Name 10FALSE12/01/2025

 

And Current (Daily imported)

Name ValueDATE 
Name 1 TRUE27/01/2025
Name 2TRUE27/01/2025
Name 3TRUE27/01/2025
Name 4FALSE27/01/2025
Name 5FALSE27/01/2025
Name 6FALSE27/01/2025
Name 7FALSE27/01/2025
Name 8FALSE27/01/2025
Name 9FALSE27/01/2025
Name 10FALSE27/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 ValueDATE VALUE Change date
Name 1 TRUE26/01/2025 
Name 2TRUE26/01/202526/01/2025
Name 3TRUE26/01/202526/01/2025
Name 4FALSE26/01/2025 
Name 5FALSE26/01/2025 
Name 6FALSE26/01/2025 
Name 7FALSE26/01/2025 
Name 8FALSE26/01/2025 
Name 9FALSE26/01/2025 
Name 10FALSE26/01/2025 
Name 1 TRUE19/01/202519/01/2025
Name 2FALSE19/01/2025 
Name 3FALSE19/01/2025 
Name 4FALSE19/01/2025 
Name 5FALSE19/01/2025 
Name 6TRUE19/01/202519/01/2025
Name 7FALSE19/01/2025 
Name 8FALSE19/01/2025 
Name 9FALSE19/01/2025 
Name 10FALSE19/01/2025 
Name 1 FALSE12/01/2025 
Name 2FALSE12/01/2025 
Name 3FALSE12/01/2025 
Name 4FALSE12/01/2025 
Name 5FALSE12/01/2025 
Name 6FALSE12/01/2025 
Name 7FALSE12/01/2025 
Name 8FALSE12/01/2025 
Name 9FALSE12/01/2025 
Name 10FALSE12/01/2025 

 

Table Current

Name ValueDATE Date Changed
Name 1 TRUE27/01/202519/01/2025
Name 2TRUE27/01/202526/01/2025
Name 3TRUE27/01/202526/01/2025
Name 4FALSE27/01/2025 
Name 5FALSE27/01/2025 
Name 6TRUE27/01/202519/01/2025
Name 7FALSE27/01/2025 
Name 8FALSE27/01/2025 
Name 9FALSE27/01/2025 
Name 10FALSE27/01/2025 

 

Could you please help? Thank you.

Tg

Need Help
  • Irwan's avatar
    Irwan
    1 year ago

    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.

3 Replies

  • Irwan's avatar
    Irwan
    Super User
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      Thank you for your help.

      • Irwan's avatar
        Irwan
        Super User
        1 year ago

        Hello Anonymous 

         

        Glad to be a help.

         

        Thank you.