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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

View solution in original post

13 REPLIES 13
ctedesco3307
Resolver II
Resolver II

  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

 

 

 

Krd603206
Frequent Visitor

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.

Anonymous
Not applicable

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

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.
Anonymous
Not applicable

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
Not applicable

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
Not applicable

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

 

I will change my model with this in mind.

 

Best regards

Martin

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.

Anonymous
Not applicable

 

// 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

D

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 

Anonymous
Not applicable

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

D

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?

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors