cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DareToTry Frequent Visitor
Frequent Visitor

Lookup value if date is between two dates

Hi

 

I'm new to PowerBI and the DAX syntax.

I have 2 tables (Sprints and WorkItems). All date columns are formatted as Date

 

Sprints table: (columns StartDate, FinishDate and SprintNumber)

Table_Sprints.PNG
 
WorkItems table: (columns CreatedDate and CreatedInSprintNumber)
Table_WorkItems.png
 
I am trying to test if the CreatedDate from WorkItems falls within the date range (Start and Finish) in Sprints and then return the SprintNumber.
 
I searched for a solution and found this expression:
CreatedInSprint =
CALCULATE(
     SUM(Sprints[SprintNo]);
     FILTER(Sprints;
                 Sprints[attributes_startDate] <= WorkItems[fields_SystemCreatedDate] &&
                 Sprints[attributes_finishDate] >= WorkItems[fields_SystemCreatedDate]
      )
)
However, - as you can see it only returns some of the sprint numbers (the ones matching Finish date)
 
Can anyone see what I am doing wrong?
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Lookup value if date is between two dates

I don't think you've told us all about the model... I suspect there are relationships between the two tables based on the date fields.

 

Try this

CreatedInSprint =
var __date = WorkItems[fields_SystemCreatedDate]
return
	MAXX(
		FILTER(
			Sprints;
			AND(
		    	Sprints[attributes_startDate] <= __date,
		    	__date <= Sprints[attributes_finishDate]
		    )
		),
		Sprints[SprintNo]
	)

This should work correctly on the assumption that there is always at most one sprint returned by the logical condition in FILTER. If there happen to be many, then the maximum SprintNo will be returned.

 

Best

Darek

4 REPLIES 4
Highlighted
Super User
Super User

Re: Lookup value if date is between two dates

I don't think you've told us all about the model... I suspect there are relationships between the two tables based on the date fields.

 

Try this

CreatedInSprint =
var __date = WorkItems[fields_SystemCreatedDate]
return
	MAXX(
		FILTER(
			Sprints;
			AND(
		    	Sprints[attributes_startDate] <= __date,
		    	__date <= Sprints[attributes_finishDate]
		    )
		),
		Sprints[SprintNo]
	)

This should work correctly on the assumption that there is always at most one sprint returned by the logical condition in FILTER. If there happen to be many, then the maximum SprintNo will be returned.

 

Best

Darek

DareToTry Frequent Visitor
Frequent Visitor

Re: Lookup value if date is between two dates

Hi Darek

 

Thank you for your rapid reply. Your assumption was correct and it works out of the box.

 

Just to follow-up on the model.

 

The following relationships exist (between Dates and Sprints) and (between Dates and WorkItems)

 

From date in Dates to attributes_startDate in Sprints (1:*) and (cross filter direction: Both)

From date in Dates to attributes_finishDate in Sprints (1:*) and (cross filter direction: Both)

From date in Dates to fields_SystemCreatedDate in WorkItems  (1:*) and (cross filter direction: Both)

 

Best regards

Martin

Super User
Super User

Re: Lookup value if date is between two dates

I want to warn you:

 

Be extremely careful with a model that has both-ways cross-filtering enabled. This is very DANGEROUS and you may end up calculating things you won't understand. The best people in the world of DAX say that both-ways cross-filtering should be enabled IF AND ONLY IF it's strictly necessary and when you understand all the consequences. I'd advise that you revise your model and remove cross-filtering as much as possible. If the model becomes at one point ambiguous (because, for instance, you add some tables to it and create relationships) and the engine does not detect it (which is not uncommon), then you'll be in deep trouble.

 

You've been warned.

 

Best

Darek

DareToTry Frequent Visitor
Frequent Visitor

Re: Lookup value if date is between two dates

I see, thank you for the insights on this topic.

 

I will change my model with this in mind.

 

Best regards

Martin

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 291 members 3,556 guests
Please welcome our newest community members: