Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 |
@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 |
@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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
74 | |
57 | |
38 | |
33 |
User | Count |
---|---|
71 | |
66 | |
57 | |
49 | |
47 |