Create a tenure column from two different tables using calendar date slicer.

[Anonymous]

Hi, i've following issue in power bi. Here are the details.

Issue: Capturing the date from a date slicer and using the selected value and use it for tenure. 
I have two date table. One is calendar date table and another one is Employee_Hire_date.

I have to calculate tenure for each date column. I'm using following sql for this. I want to implement this on power BI but i'm facing issue as these two table are not joined with each other. I want the ans in column respective to each hire date.

Tenure = (Calendar(date)-Employee_Hire_date)/365.23

Its not giving me right tenure count since these two tables are not connected. 

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

As we know, the data in calculated column is not dynamic based on the selected values in a slicer. So we can create measure to work on it. 

Tenure = var _date=SELECTEDVALUE('calendar'[Date])-MAX('Table1'[date])
return
IF(_date>0,_date/365.23,BLANK())

Regards,

Frank

[Anonymous]

Hi,

Thanks for the solution. It really works. But in this case i have to calculate the average of the resulted output. And i'm not able to do it using average function as it is in measure.

Could you please provide a solution where i can calculate the average of the resulted measure as i want to display the value in advance card?

[v-frfei-msft]

Hi @Anonymous ,

To create another new measure.

Measure =
SUMX ( Table1, [Tenure] )
    / CALCULATE (
        DISTINCTCOUNT ( Table1[Employee_Hire] ),
        FILTER ( Table1, [Tenure] <> BLANK () )
    )

Regards,

Frank

[Anonymous]

Hi, I tried to use this dax but it's giving me wrong count. My employee headcount is a measure hence i used it but its giving me wrong count. I think i've to filter it by employee ids. Could you check it once?