Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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. 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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

     

4 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi Anonymous ,

         

        To create another new measure.

         

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

         

        Regards,

        Frank