Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more
I'm having trouble creating a measure to display previous dates from an order date column based on a parameter. I tried to make it a calculated column until I learned that parameters don't update in those columns. Ultimately, I'd like to be able to compare the previous date against the current order date in a row and calculate the difference (again inside a measure).
Using Window functions and offsets have gotten me close, but I keep ending up with a blank column. Alternatively, some of the measures I've tried produce values, but don't give me the previous (see measure below)
Desired
When the parameter([Number of Orders Value]) is set to 2
| Customer | Order Number | OrderDate | Previous |
| 1 | Foo1 | 9/10/2024 | 9/10/2024 |
| 1 | Foo2 | 9/12/2024 | 9/10/2024 |
| 1 | Foo3 | 9/13/2024 | 9/12/2024 |
| 1 | Foo4 | 9/19/2024 | 9/13/2024 |
| 2 | Bar1 | 9/12/2024 | 9/12/2024 |
| 2 | Bar2 | 9/15/2024 | 9/12/2024 |
| 2 | Bar3 | 9/19/2024 | 9/15/2024 |
| 2 | Bar4 | 9/20/2024 | 9/19/2024 |
Solved! Go to Solution.
Sometimes it just takes asking to find the answer. I was very close with the DAX I posted, I just had to add "ALLSELECTED" to the relation to make it work.
Final DAX, Change in bold
Previous = MINX(WINDOW(-[Number of Orders Value],REL,0,REL,ALLSELECTED(OrderTable),ORDERBY([Order Date],ASC),,PARTITIONBY([Customer]),MATCHBY([Order Number])),OrderTable[Order Date])
Sometimes it just takes asking to find the answer. I was very close with the DAX I posted, I just had to add "ALLSELECTED" to the relation to make it work.
Final DAX, Change in bold
Previous = MINX(WINDOW(-[Number of Orders Value],REL,0,REL,ALLSELECTED(OrderTable),ORDERBY([Order Date],ASC),,PARTITIONBY([Customer]),MATCHBY([Order Number])),OrderTable[Order Date])
The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!
| User | Count |
|---|---|
| 40 | |
| 35 | |
| 34 | |
| 31 | |
| 27 |
| User | Count |
|---|---|
| 136 | |
| 102 | |
| 68 | |
| 66 | |
| 58 |