Forum Discussion

mhayes2's avatar
mhayes2
Frequent Visitor
8 years ago
Solved

lookup data inside a row entry history table

Hi all,   I am hoping someone can help me as this is starting to drive me crazy ;-)   I have a history table that is updated with a new row each time an action is performed on an Item (its a shar...
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi mhayes2,

     

    Please check out the demo here.

    Add a calculated column like this:

    days =
VAR ItemId = [ItemID]
VAR OldState = [OldState]
VAR ModifiedDate =
    CALCULATE (
        MIN ( [ModifiedDate] ),
        FILTER ( 'Table1', 'Table1'[ItemID] = itemid && 'Table1'[NewState] = oldstate )
    )
RETURN
    DATEDIFF ( ModifiedDate, [ModifiedDate], DAY )

    lookup_data_inside_a_row_entry_history_table

     

    Best Regards,

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi mhayes2,

 

Please check out the demo here.

Add a calculated column like this:

days =
VAR ItemId = [ItemID]
VAR OldState = [OldState]
VAR ModifiedDate =
    CALCULATE (
        MIN ( [ModifiedDate] ),
        FILTER ( 'Table1', 'Table1'[ItemID] = itemid && 'Table1'[NewState] = oldstate )
    )
RETURN
    DATEDIFF ( ModifiedDate, [ModifiedDate], DAY )

lookup_data_inside_a_row_entry_history_table

 

Best Regards,

Dale

mhayes2's avatar
mhayes2
Frequent Visitor
8 years ago

Thanks for your help !! This worked perfectly :-)