Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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) ...
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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

       

      I will change my model with this in mind.

       

      Best regards

      Martin

      • Thimios's avatar
        Thimios
        Icon for Helper III rankHelper III
        6 years ago

        Hi Anonymous,

        Could you pls advise for a similar issue?

        1st table is a typical Calendar table (column Date is of interest).

        2nd table has the following columns: Opportunity ID, Opp Start Date, Opp Close Date, Opp value.

        I want to get the cumulative value of valid Opportunities at 'Calendar'[Date] hierarchy. An Opportunity is considered valid when Date is between the Opp Start Date and the Opp Close Date.

        Thank you for your effort.