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

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

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's avatar
    Anonymous
    Not applicable
    6 years ago
    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