Forum Discussion

cinderelly678's avatar
cinderelly678
Frequent Visitor
3 years ago
Solved

Help Flagging Status Change

I want to have a measure that will display 1 when a status has changed, and 0 when the status is the same as yesterday's value. My data set is rather large, so I want to avoid using a calculated colu...
Need Help
Tips and Tricks
Tutorial Requests
  • foodd's avatar
    foodd
    3 years ago

    Use the following DAX to create the column:

     

    Change Status Flag = 
VAR CurrentDate = 'Table'[Snapshot Date]
VAR CurrentKey = 'Table'[Key]
VAR CurrentStatus = 'Table'[Status]
VAR PreviousStatus =
    CALCULATE(
        FIRSTNONBLANK('Table'[Status], 1),
        FILTER(
            'Table',
            'Table'[Snapshot Date] = CurrentDate - 1 &&
            'Table'[Key] = CurrentKey
        )
    )
RETURN IF(ISBLANK(PreviousStatus), 0, IF(CurrentStatus <> PreviousStatus, 1, 0))

     

     

     

foodd's avatar
foodd
Community Champion
3 years ago

Use the following DAX to create the column:

 

Change Status Flag = 
VAR CurrentDate = 'Table'[Snapshot Date]
VAR CurrentKey = 'Table'[Key]
VAR CurrentStatus = 'Table'[Status]
VAR PreviousStatus =
    CALCULATE(
        FIRSTNONBLANK('Table'[Status], 1),
        FILTER(
            'Table',
            'Table'[Snapshot Date] = CurrentDate - 1 &&
            'Table'[Key] = CurrentKey
        )
    )
RETURN IF(ISBLANK(PreviousStatus), 0, IF(CurrentStatus <> PreviousStatus, 1, 0))

 

 

 

cinderelly678's avatar
cinderelly678
Frequent Visitor
3 years ago

This works brilliantly and is very handy to have in the arsenal. Thank you so much foodd , you made my Friday night!

  • foodd's avatar
    foodd
    Community Champion
    3 years ago

    I take Kudos anytime.  Glad that I could help!