Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Nellis5
Regular Visitor

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)

 

Previous = MINX(WINDOW(-[Number of Orders Value],REL,0,REL,OrderTable,ORDERBY([Order Date],ASC),,PARTITIONBY([Customer]),MATCHBY([Order Number])),OrderTable[Order Date])

 

 

Desired

 

When the parameter([Number of Orders Value]) is set to 2

 

CustomerOrder NumberOrderDatePrevious
1Foo19/10/20249/10/2024
1Foo29/12/20249/10/2024
1Foo39/13/20249/12/2024
1Foo49/19/20249/13/2024
2Bar19/12/20249/12/2024
2Bar29/15/20249/12/2024
2Bar39/19/20249/15/2024
2Bar49/20/20249/19/2024
1 ACCEPTED SOLUTION
Nellis5
Regular Visitor

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])

View solution in original post

1 REPLY 1
Nellis5
Regular Visitor

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])

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.