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

Krd603206's avatar
Krd603206
Frequent Visitor
4 years ago

Hi @Anonymous, would you be able to help with a similar question. I have a table in which i am looking to return true/false for a date which comes after the Date_from column and before the Date_until column date. I have tried a regular DAX expression of 

InRangeDate = and(DELNOTES[DELNOTE_DATE]>=DELNOTES[DATE_FROM],DELNOTES[DELNOTE_DATE]<=DELNOTES[DATE_UNTIL]) but this only returns TRUE in all instances. I have then tried to hard code the date for a given accounting period using this expression 

InRange = and(DELNOTES[DELNOTE_DATE]>=date(2021,11,22),DELNOTES[DELNOTE_DATE]<=date(2021,12,26)) - this works perfectly, but is not dyamic. The issue appears to be with the calculation but am at a loss as to how to get around it. Any help would be most helpful.