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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 2
Greg_Deckler
Super User
Super User

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?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

StageIDSiteIDStageNameStageComplete
11Stage 12019-5
21Stage 22019-9
32Stage 12019-7

 

Program Stages

SiteIDProgramTypeDateProgramAvailableConstruction Stage ID (calculated column)
1Dept12019-51
1Dept22019-82
2Dept32019-63

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors