Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@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 output
Source 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 |
@Abbasi 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
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |