Forum Discussion
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):
| Title | Amount | Salesperson | Date |
The snapshot also adds a "Snapshot date" column. it's relevant headers look like:
| Title | Amount | Salesperson | Date | Snapshot 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
- GeraldGEmerick
Memorable Member
Try using FILTER instead of CALCULATETABLE Cayshin or possibly FILTER( ALL ( instead of CALCULATETABLE
- jgeddes
Super User
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() ) - CayshinFrequent Visitor
Thank you GeraldGEmerick & jgeddes! Switching to FILTER() made it feel much more intuitive for me to understand what's happening too. I'm getting the right results now.