Forum Discussion

Cayshin's avatar
Cayshin
Frequent Visitor
26 days ago
Solved

How to compare static table to updated table, find rows that changed?

At the start of each month, I take a snapshot (TABLE1) of a live data table (TABLE2). TABLE2 has three relevant columns that defines a unique row (Title, Amount, Salesperson), and a fourth column that can change (Date):

TitleAmountSalespersonDate

The snapshot also adds a "Snapshot date" column. it's relevant headers look like:

TitleAmountSalespersonDateSnapshot Date

There's a table visual to display these snapshot items and a Year, Month slicer (Relationship: CalenderTable[Date] -> TABLE1[Snapshot Date])  to filter the snapshots to only a specific month.

 

 

 

 

I now want to compare these snapshot items to the live data table and have the table visual only show rows where the Date column changed to a later date. 

After some googling, I created a Measure I can use to filter the table visual to values of "1", but the date comparison doesn't seem to be working right. It's returning some rows where the Date in TABLE2 was changed to earlier (we don't care about those) than the Date in TABLE1 and I can't figure out why.

Rolled = If( COUNTROWS(     CALCULATETABLE(TABLE2 ,              TABLE2[Name] = SELECTEDVALUE(TABLE1[Name])         && TABLE2[Amount] = SELECTEDVALUE(TABLE1[Amount])         && TABLE2[Salesperson] = SELECTEDVALUE(TABLE1[Salesperson])         && TABLE2[Date] > SELECTEDVALUE(CalendarTable[Date])         )     ) > 0,     1,     0 )

Example of wrongly listed TABLE1 row:

TABLE2 is not connected by any relationships. I manually applied filters to show the same row to see if the measure worked properly.

What am I doing wrong or is there a better way to do this?

  • Building on what GeraldGEmerick​ mentioned.
    Here is some code that may point you in the right direction.

    Rolled =  var _date =  CALCULATE(MAX(Table2[Date]), FILTER(Table2, Table2[Title] = SELECTEDVALUE(Table1[Title]) && Table2[Salesperson] = SELECTEDVALUE(Table1[Salesperson]) && Table2[Amount] = SELECTEDVALUE(Table1[Amount]))) RETURN IF(     _date > SELECTEDVALUE(Table1[Date]) && _date < SELECTEDVALUE(Table1[Snapshot Date]),     1,     BLANK() )

     

3 Replies

  • Building on what GeraldGEmerick​ mentioned.
    Here is some code that may point you in the right direction.

    Rolled =  var _date =  CALCULATE(MAX(Table2[Date]), FILTER(Table2, Table2[Title] = SELECTEDVALUE(Table1[Title]) && Table2[Salesperson] = SELECTEDVALUE(Table1[Salesperson]) && Table2[Amount] = SELECTEDVALUE(Table1[Amount]))) RETURN IF(     _date > SELECTEDVALUE(Table1[Date]) && _date < SELECTEDVALUE(Table1[Snapshot Date]),     1,     BLANK() )