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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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