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 im...
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.

     

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
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.

 

cchp07's avatar
cchp07
Frequent Visitor
6 years ago

v-frfei-msft This works perfect, but what if there were multiple item #'s?