topic Re: Lookup value if date is between two dates in DAX Commands and Tips

Lookup value if date is between two dates

Anonymous — Wed, 17 Jul 2019 11:59:45 GMT

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)

WorkItems table: (columns CreatedDate and CreatedInSprintNumber)

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?

Re: Lookup value if date is between two dates

Anonymous — Wed, 17 Jul 2019 22:38:02 GMT

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

Re: Lookup value if date is between two dates

Anonymous — Fri, 19 Jul 2019 10:09:56 GMT

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

Re: Lookup value if date is between two dates

Anonymous — Fri, 19 Jul 2019 10:16:28 GMT

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

Re: Lookup value if date is between two dates

Anonymous — Fri, 19 Jul 2019 11:10:08 GMT

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

I will change my model with this in mind.

Best regards

Martin

Re: Lookup value if date is between two dates

Thimios — Wed, 25 Mar 2020 10:10:22 GMT

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.

Re: Lookup value if date is between two dates

Anonymous — Wed, 25 Mar 2020 11:09:10 GMT

// Calendar can be connected via Date // to any or both of the columns in Opportunities. // This does not matter for this calculation. // If there are any relationships, the // CROSSFILTER function will remove the // relationships. If there are no relationships // you might need to remove the function from // the code. Assumption is that each opportunity // has a start date and a close date that's not // blank. If an opportunity is still valid today // the end date will be, say, coded as 3000-01-01. // No blanks allowed. BLANKS will complicate the // code and make it slower. [Valid Opp Count] = var __dateSelected = SELECTEDVALUE( Calendar[Date] ) var __isDateDirectlyFiltered = ISFILTERED( Calendar[Date] ) var __count = CALCULATE( Opportunities, Opportunities[Start Date] <= __dateSelected, __dateSelected <= Opportunities[End Date] // If you allow End Date to be blank, then you // have to add this to the above expression // || ISBLANK( Opportunities[End Date] ) // Out of the two select the correct one // or remove them if there is no relationship // from Date to these columns. CROSSFILTER( 'Calendar'[Date], Opportunities[Start Date], NONE ), CROSSFILTER( 'Calendar'[Date], Opportunities[End Date], NONE ) ) return if( __isDateDirectlyFiltered, __count )

Best

Re: Lookup value if date is between two dates

Thimios — Wed, 25 Mar 2020 21:28:22 GMT

Thank you Anonymous for your ideas. I've done some testing but I didn't manage to get any results.

Do you mind taking a look at the sample pbix file here?

https://drive.google.com/open?id=1nZGsdNwTTDNcyrjDmnPJxdl589M1C5fk

Re: Lookup value if date is between two dates

Anonymous — Thu, 26 Mar 2020 00:37:08 GMT

Hi there. I've done some work on this but I'm too tired right now to make it the way it should be. I've noticed that, for instance, the calendar does not handle missing dates properly. Dates should be handled in such a way that when there's no date, BLANK is not left in such a field but a dedicated date (e.g., 3000-01-01) as assigned to it and this date is present in the Calendar as well. The real Date field in the Calendar should be hidden and a date-like text should be presented to the user. The special dates that handle missing dates should have a label like Unknown or maybe 'Not Started' or 'Not Finished'... Something of this kind. But BLANKS should be avoided as much as possible because they make calculations not only more complex but also slower. Having said all that... I attached a file with what you wanted. Please take a look at how I handled the opportunities that do not have a start date. If an opportunity does not have a start date, it means this opportunity does not exist.

Best

Re: Lookup value if date is between two dates

Thimios — Thu, 26 Mar 2020 13:41:02 GMT

Great advice Anonymous, thank you!

I made all necessary changes as far as Calendar is concerned and results are verified on day basis.

I've noticed though that Date Hierarchy is not available to use in the visual. Is filtering inside the '# Opportunities' measure responsible for that?

Re: Lookup value if date is between two dates

Anonymous — Thu, 26 Mar 2020 14:13:48 GMT

Nope. The Date hierarchy that's generated by Power BI automatically should NEVER be relied upon but in the simplest of models. If you have a decent model and want to keep your sanity, you should never rely on this functionality. It's for newbies who have no idea what a good model is and what good DAX means. So, please stay away from it. You should always, ALWAYS, have a calendar of your own with all the entities defined in it. The functionality to create an automatic hierarchy can be switched no/off in the settings of the file or globally. My strong suggestion is to turn it off forever and forget it has ever existed. There are numerous YT videos by Marco Russo and Alberto Ferrari that say you should forget this functionality unless... you like asking for troubles. Make you own calendar and you'll be safe.

Best
D

Re: Lookup value if date is between two dates

Krd603206 — Tue, 14 Dec 2021 09:42:29 GMT

Hi @Anonymous, would you be able to help with a similar question. I have a delivery note date that i wish to determine is within my period dates, (From and To) which are in the same table. I have tried the regular DAX expression of and([DELNOTE_DATE]>=[date_from],[DELNOTE_DATE]<=[date_to]) but i just get all true results. I have hard coded the date using and([DELNOTE_DATE]>=date(2021,11,22),[DELNOTE_DATE]<=date(2021,12,26)) and this works perfectly? AlI columns are set as date. Any help/guidance would be appreciated.

Re: Lookup value if date is between two dates

Krd603206 — Tue, 14 Dec 2021 15:31:39 GMT

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.

Re: Lookup value if date is between two dates

ctedesco3307 — Fri, 20 May 2022 18:08:30 GMT

I’m trying to create a formula to show the QBEstimate.Monthlyfee for the righg billing period

My qBEstimate Table has a QBEstimate.EsStartDate, QBEstimate.EsEndDate and monthly fee. I’m trying to create a matrix to show the fee by TDate.Billing Month

I know the problem is in my relationships but I can’t set the set QBEstimate.EsStartDate and QBEstimate.EsEndDate to the TDate.Billing Month

This is my measure – it returns te right values for some months but not all.

DAX measure

MSSMonthlyFees =

CALCULATE(

SUM(QBEstimate[MonthlyFee]),

FILTER(QBEstimate,

QBEstimate[EsStartDate] <= min(TDate[Billing Month]) &&

QBEstimate[EsEndDate] >= max(TDate[Billing Month])

)

All help welcome

Thank you

TDATE Table

TDate = ADDCOLUMNS(

CALENDAR(date(2021,1,1), date(2022,12,31)),

"Month", FORMAT([Date],"mmm YY"),

"MonthOrder", MONTH([Date]),

"Year",YEAR([Date]),

"Week", WEEKNUM([Date]),

"WeekYear", concatenate(YEAR([Date]),WEEKNUM([Date])),

"Billing Month",

VAR DayNumber = WEEKDAY ( [Date], 1 ) RETURN IF(DayNumber = 7,[Date] - 1, [Date] + 6 - DayNumber)

)

QBEstimate Table

Id	CustomerRef_Value	EsStartDate	ESEndDate	MonthlyFee
17563	1252	4/21/2022	10/22/2022	$9,900.00
17558	1247	4/1/2022	4/1/2023	$21,991.67
17494	1185	2/13/2022	2/13/2023	$19,227.67
17531	1216	8/21/2021	8/19/2022	$25,695.00
17530	1215	8/19/2021	8/19/2022	$10,075.00
17492	1183	7/30/2021	10/22/2022	$4,070.30
17518	1204	7/1/2021	5/1/2022	$20,720.74
17487	1159	6/30/2021	6/30/2022	$35,000.00
17523	1165	8/22/2020	10/22/2022	$15,578.81