Forum Discussion

Honne2021's avatar
Honne2021
Helper II
3 years ago
Solved

Tricky Dax Measure For Dynamic and Static End Dates

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/m-p/352830

 





 

General Comment
Need Help
Tutorial Requests

2 Replies

    • Honne2021's avatar
      Honne2021
      Helper II
      3 years ago

      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