Calculate Experience in Dax

[sheik]

I have Employee Table and Date Table.

I have a slicer of Year Month. Based on the selection the summary dashboard shows the Active head count as on last day of the month seleceted in year month slicer.

From the summary page i have drill through which shows the active employee details. The drill through works fine. 

The issue is I am not able to caculate the experience based on the DOJ and the last day of the month which I selected in the Year Month Slicer.

e.g: The DOJ is 1st Jan 2020 then If I choose Dec2020 then the exp should be displayed as 1 year and if I choose Jun2021 then it should show as 1.5 years.

How do I achieve using the DAX?

I tried the below DAX, but it always calculates the experience as of today not as of the selection.

Exp =

    VAR Total_Exp_Tmp   =

        IF(

                Employee[STATUS]=="S",

                ROUND(DATEDIFF(Employee[JOIN_DATE],Employee[EXIT DATE],MONTH)/12,2),

                ROUND(DATEDIFF(Employee[JOIN_DATE],Max(Dates[Date]),MONTH)/12,2)

        )

    Return Total_Exp_Tmp

[Greg_Deckler]

@sheik Try EOMONTH(Max(Dates[Date]),0)

[sheik]

It doenst help, since Max(Dates[Date]) retuns the max date in the table which is always today date. It doesnt show the last day of the selected month. FYI I am creating a new column in the Employee table using the above dax. Not creating as the measure

[Greg_Deckler]

@sheik Well that makes no sense then. Calculated columns are calculated when the data is loaded and refreshed. Calculated columns are not interactive and don't interact with slicers so you are never going to be able to achieve what you want with a calculated column, you need to use a measure.