Forum Discussion
Not allowing for date within Range
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
2 Replies
- Greg_Deckler
Community Champion
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?
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.
