Forum Discussion
Comparing two records in the same table, on selected dates, using DAX.
Looking for a suggestion on how best to compare two records in the same table using DAX?
If record does not exit on the specified date then the previous record, by date, is selected for the comparison.
Given below is an example comparing sale on two different dates in the same table.
User selects 16/01/2020 and 19/01/2020 from two date slicers respectively for comparison.
If the record does not exist on the date then value from previous date is considered
Store ID | Date | Sale |
A | 14/01/2020 | 1000 |
A | 15/01/2020 | 900 |
A | 18/01/2020 | 1100 |
A | 21/01/2020 | 1200 |
B | 15/01/2020 | 2000 |
B | 19/01/2020 | 3000 |
C | 14/01/2020 | 5500 |
D | 24/01/2020 | 7000 |
I’m looking for the following results
First Sale Date Selected from Slicer 16/01/2020 | Second Sale Date Selected from Slicer 19/01/2020 | |
A | 900 | 1100 |
B | 2000 | 3000 |
C | 5500 | NA |
D | NA | NA |
4 Replies
Anonymous I would use disconnected date tables for your slicers. You can get the values in these using SELECTEDVALUE. Then you would FILTER the table based upon this date and anything less than that date. You would then use MAXX to get the max date in the table and then MAXX and FILTER again to get the Value at that date. Good general example is Lookup Min/Max. https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
Greg_Deckler I have updated the required output above. Thanks for your feedback
- Date Two Slicer condition : =IF (SELECTEDVALUE ( Dates_Two[Date_Two] ) > SELECTEDVALUE ( Dates_One[Date_One] ),1,0)Sales total by DateOne : =VAR _currentstore =MAX ( Stores[Store ID] )VAR _selecteddate =SELECTEDVALUE ( Dates_One[Date_One] )VAR _maxdate =CALCULATE (MAX ( Sales[Date] ),FILTER (ALL ( Sales ),Sales[Store ID] = _currentstore&& Sales[Date] <= _selecteddate))RETURNIF (HASONEVALUE ( Stores[Store ID] ),COALESCE (CALCULATE (SUM ( Sales[Sale] ),FILTER (ALL ( Sales ),Sales[Store ID] = _currentstore&& Sales[Date] = _maxdate)),"NA"))Sales total by DateTwo : =VAR _currentstore =MAX ( Stores[Store ID] )VAR _selecteddate =SELECTEDVALUE ( Dates_Two[Date_Two] )VAR _selecteddateDateOne =SELECTEDVALUE ( Dates_One[Date_One] )VAR _maxdate =CALCULATE (MAX ( Sales[Date] ),FILTER (ALL ( Sales ),Sales[Store ID] = _currentstore&& Sales[Date] <= _selecteddate))RETURNIF (HASONEVALUE ( Stores[Store ID] ),COALESCE (CALCULATE (SUM ( Sales[Sale] ),FILTER (ALL ( Sales ),Sales[Store ID] = _currentstore&& Sales[Date] = _maxdate&& Sales[Date] > _selecteddateDateOne)),"NA"))
Jihwan_Kim Thanks for the detailed input. This is very close to my requirement.
However, I have around 20 more fields to show from the Sales table displaying only one row for each Store as shown before. Here is more detailed example of source and desired outputSource Data:
Store ID Date Sale Sales Agent Commission A 14/01/2020 1000 Maria 10 A 15/01/2020 900 Dave 15 A 18/01/2020 1100 Andy 20 A 21/01/2020 1200 Ali 30 B 15/01/2020 2000 Kate 40 B 19/01/2020 3000 Rob 50 C 14/01/2020 5500 Peter 60 D 24/01/2020 7000 Mike 70 Desired Results 1 Store ID Sales total by DateOne : Sales total by DateTwo : Agent by Date One Agent by Date Two Commission by Date One Commission by Date Two A 900 1100 Dave Andy 15 20 B 2000 3000 Kate Rob 40 50 C 5500 5000 Peter Peter 60 60 D NA NA NA NA NA NA Desired Results 2 Use Only Date One Filter Store ID Sales total by DateOne : Agent by Date One Commission by Date One A 900 Dave 15 B 2000 Kate 40 C 5500 Peter 60 D NA NA NA
