Forum Discussion

deanbland's avatar
deanbland
Icon for Helper III rankHelper III
5 years ago
Solved

How to flag status change

Hi,    I have a dataset like this:    ID Date Status ID1 22/08/2020 Open ID2 22/08/2020 Open ID3 22/08/2020   ID1 22/09/2020 Closed ID2 22/09/2020 Open   I am w...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi deanbland ,

     

    Please try the following formula:

     

    Column = 
var PreDate = 
    MAXX(
        FILTER(
            'Table',
            'Table'[Date] < EARLIER('Table'[Date])
            && 'Table'[ID] = EARLIER('Table'[ID])
        ),
        'Table'[Date]
    )
var PD_Status = 
    CALCULATE(
        MAX('Table'[Status]),
        FILTER(
            'Table',
            'Table'[Date] = PreDate 
            && 'Table'[ID] = EARLIER('Table'[ID])
        )
    )
return 
    IF( PD_Status = BLANK() || PD_Status = 'Table'[Status], 0, 1 )

     

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

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

Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
5 years ago

deanbland 

I added a new column, also add one more record to check the scenario in the sample data.

Change = 
INT(
    COUNTROWS(
        FILTER(
            Table2,
            Table2[ID] = EARLIER(Table2[ID]) && FORMAT(Table2[Date],"yymm") = FORMAT(EARLIER(Table2[Date]),"yymm") && Table2[Status] <> BLANK()
        )
    )>1
)

 



deanbland's avatar
deanbland
Icon for Helper III rankHelper III
5 years ago

Hi Fowmy , 

 

I have tried your suggestion and it isn't picking up the status changes. The highlighted column on the right shows the output from the DAX you provided, which is not the expected outcome as the status change from Open to Close should have been marked. 

 

 

DateIDStatusStatus Change
28/06/2021RID17Open0
05/07/2021RID17Closed1
02/08/2021RID17Closed0

 

Above shows an exact cut from the data, only including the columns needed and filtered on the example in the picture. The end column is the expected output. How would I go about editing your DAX to provide this outcome? 

 

Thanks for all your help so far!

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi deanbland ,

     

    Please try the following formula:

     

    Column = 
var PreDate = 
    MAXX(
        FILTER(
            'Table',
            'Table'[Date] < EARLIER('Table'[Date])
            && 'Table'[ID] = EARLIER('Table'[ID])
        ),
        'Table'[Date]
    )
var PD_Status = 
    CALCULATE(
        MAX('Table'[Status]),
        FILTER(
            'Table',
            'Table'[Date] = PreDate 
            && 'Table'[ID] = EARLIER('Table'[ID])
        )
    )
return 
    IF( PD_Status = BLANK() || PD_Status = 'Table'[Status], 0, 1 )

     

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Does something like this work if you have two seperate tables.... I have two imports. One from the previous week and one from this week. And I need to find the change between the two tables