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 sharepoint list).

The Columns are :

 

ID | ItemID | OldState | NewState| ModifiedDate |

 

The ID col is unique but there will be many entries for each ItemId

 

What i need is find the number of days that an Item was in the old state

So i need to look back up the table and find a row that has the same ItemID and the NewState is equal to this rows OldState and return the modified date.

I can then do a date diff between the two modified dates to give me the number of days the item was in the old state.

 

But i just cant find a way !!

Any help would be very much appreaciated :-)

 

Thanks

Martin

 

 

  • 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

2 Replies

  • 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 :-)