Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
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?
Hi @Anonymous ,
To create another new measure.
Measure = SUMX ( Table1, [Tenure] ) / CALCULATE ( DISTINCTCOUNT ( Table1[Employee_Hire] ), FILTER ( Table1, [Tenure] <> BLANK () ) )
Regards,
Frank
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?
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |