topic Re: Not allowing for date within Range in DAX Commands and Tips

Not allowing for date within Range

Anonymous — Wed, 06 May 2020 20:10:51 GMT

Hi all,

I'm using the following formula for a custom column (that I pulled off of here, somewhere) to find if a row matches to a phase.

Construction Phase ID = 

VAR StageDate = prog_SiteProgramStages[Program Available - Year]

RETURN

 MAXX(

 FILTER(

 site_ConstructionPhases,

 site_ConstructionPhases[Year Complete] <= StageDate &&

 StageDate <= site_ConstructionPhases[Year Complete]

 ),

 site_ConstructionPhases[PhaseID]

 )

It works, except that it is basically performing an Exact Match. The Phases cover mutliple years, so a Row whose year falls in the time span of the Phase doesn't return a result. But if the row's year matches the end year for a phase the result returns correctly. How do a I modify to allow for the row's date to fall within each range defined by each Phase?

Thanks,

-R

Re: Not allowing for date within Range

Greg_Deckler — Wed, 06 May 2020 21:04:03 GMT

Difficutl without data to visualize. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

But I'm guessing it involves checking if your date is greater than or equal to Year Started?

Re: Not allowing for date within Range

Anonymous — Thu, 07 May 2020 13:24:09 GMT

So changing the first part of the Filter to look at Phase Start date helped with that condition. However I have another state where there are only end dates. So data looks something like this:

Construction Stages

StageID	SiteID	StageName	StageComplete
1	1	Stage 1	2019-5
2	1	Stage 2	2019-9
3	2	Stage 1	2019-7

Program Stages

SiteID	ProgramType	DateProgramAvailable	Construction Stage ID (calculated column)
1	Dept1	2019-5	1
1	Dept2	2019-8	2
2	Dept3	2019-6	3

Right now, I only return a Stage ID when the year & month match, but as you see, I don't have start dates, nor do I want to have to include start dates, I'd prefer to infer it from the data available, ie one stage will be complete before the next stage "starts". The point is to dynamically solve for what Construction Stage a Program Stage will fall into, rather than having the user have to define that information in advance. It allows the user to input construction and program stages seperate from each other and then see where the chips fall based on their input.