Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Comparing rows in same table to verify what value has changed

Hi

I have researched for an answer to this problem but yet to find the right answer.

 

I have a dataset with columns, ID, Date, Development Type, Delivery Term and would like to see what data has change from one week to the next (if Delivery Term has changed to be specific). All data is within a single table.

 

I need to be able to show (using conditional formatting, this part I am okay with) rows in a table that have been updated.

 

Sample Data (visually you can see PRJ003 has had Delivery Term changed, how is the comparison made?)

IDDateDevelopment TypeDelivery Term
PRJ0013/03/2021NewTerm 1
PRJ0023/03/2021ImprovementTerm 2
PRJ0033/03/2021ImprovementTerm 2
PRJ0043/03/2021ImprovementTerm 3
PRJ0053/03/2021NewTerm 4
PRJ0063/03/2021NewTerm 4
PRJ00110/03/2021NewTerm 1
PRJ00210/03/2021ImprovementTerm 2
PRJ00310/03/2021ImprovementTerm 3
PRJ00410/03/2021ImprovementTerm 3
PRJ00510/03/2021NewTerm 4
PRJ00610/03/2021NewTerm 4

 

Thanks and appreciate the assistance

Need Help
  • HotChilli's avatar
    HotChilli
    5 years ago

    This will give you a column of the previous value:

    DiffFromPrvWeek = VAR _id = TablePrj[ID]
VAR _term = TablePrj[Delivery Term]
VAR _date = TablePrj[Date]
RETURN
CALCULATE(MIN(TablePrj[Delivery Term]), FILTER(ALL(TablePrj),  TablePrj[ID] = _id && TablePrj[Date] = _date -7))

    You can use it to create another column to signal a difference (from delivery term) or use an IF statement within the formula.

2 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    5 years ago

    This will give you a column of the previous value:

    DiffFromPrvWeek = VAR _id = TablePrj[ID]
VAR _term = TablePrj[Delivery Term]
VAR _date = TablePrj[Date]
RETURN
CALCULATE(MIN(TablePrj[Delivery Term]), FILTER(ALL(TablePrj),  TablePrj[ID] = _id && TablePrj[Date] = _date -7))

    You can use it to create another column to signal a difference (from delivery term) or use an IF statement within the formula.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

    Hello how about I need to compare recipes from the same table, by different scepters, and material to see if they match by the amount loaded in the components:

    and put in a column if it matches or not.