Tricky Dax Measure For Dynamic and Static End Dates

[Honne2021]

Hi, 

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.

https://community.powerbi.com/t5/Desktop/Dynamic-Age-Calculation-Based-on-User-Selected-Cutoff-Date/...

 

[amitchandak · Accepted solution]

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

[Honne2021]

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