Forum Discussion
How to return the previous date using a parameter selected range
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 |
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])
1 Reply
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])