cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
valentina14 Frequent Visitor
Frequent Visitor

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

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

Hi @valentina14 ,

 

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())

 

Capture.PNG

 

Regards,

Frank

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support Team
Community Support Team

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

Hi @valentina14 ,

 

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())

 

Capture.PNG

 

Regards,

Frank

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
valentina14 Frequent Visitor
Frequent Visitor

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

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?

Community Support Team
Community Support Team

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

Hi @valentina14 ,

 

To create another new measure.

 

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

2.PNG

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
valentina14 Frequent Visitor
Frequent Visitor

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

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?