Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II
Helper II

Tricky Dax Measure For Dynamic and Static End Dates


I cannot seem to find a solution to this! This is quite tricky and complicated as it involves 3 different scenarios with static and dynamic end date. 

If the cell is blank, use the date filter and the 'Start Date' but if there is a value, use that value as an end date but use the 'First Hire' date instead. I cannot turn it into a calculated column because the end date must be based on the date filter. I've attached a screenshot of the excel file as reference.. From what I read, calculated columns in power bi is static so I'm not sure if I should use one in here as the months in position is based on the date filter. 


 This is similar to the post found in the link below but mine involves an end date that varies depending on the scenario.


Super User
Super User

@Honne2021 , Refer if the attached file can help on similar data



View solution in original post

Super User
Super User

@Honne2021 , Refer if the attached file can help on similar data



Hi @amitchandak. I was close enough. I still have another issue. I have multiple rows of data on of each person. I had tried changing your code:

Count in Bucket 1 = sumx( values('Age Ranges'[Range]) ,CALCULATE(Countx(filter(Employee,[Current Age Buckets] = Max('Age Ranges'[ID]) ),[Current Age]), CROSSFILTER('Date'[Date],Employee[Start Date],None)))

and changed it from Sumx to Countx but I got just a count of all the range which is 3 (because I have 3 rows in the range dimension table). 

thank you

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors