Forum Discussion

cchp07's avatar
cchp07
Frequent Visitor
6 years ago
Solved

If Row Item Changes Based on Time Then Flag

I am trying to create a report item that shows when an item is changed in a row based on the time or timestamp. I need to create a custom column that shows a flag when the item has changed. In the image below you can see what the outcome should be. The date is the same day and it should only see the change by time and status #. 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi cchp07 ,

     

    Insert an index column in power query as below.

     

    Then we can create a calculated column to get the excepted reuslt.

    Column = 
VAR i_1 = 'Table'[Index] - 1
VAR i1 = 'Table'[Index] + 1
VAR st_1 =
    CALCULATE (
        MAX ( 'Table'[Status #] ),
        FILTER ( 'Table' , 'Table'[Index] = i_1 )
    )
VAR st1 =
    CALCULATE (
        MAX ( 'Table'[Status #] ),
        FILTER ( 'Table' , 'Table'[Index] = i1 )
    )
RETURN
    IF (
        st_1 <> 'Table'[Status #]
            && st1 = 'Table'[Status #],
        "out",
        IF (
            st_1 <> 'Table'[Status #]
                && 'Table'[Status #] <> st1
                && i_1 > 0,
            "in",
            BLANK ()
        )
    )

     

    For more details, please check the pbix as attached.

     

3 Replies