Average of Employee Experience monthwise from date of joining.

[Subhamoy]

Hi,

I have a table with employee Details.
sample data: -

emp_id    emp_name    date_of_joining
1               A                   14-01-2023        
2               B                   18-01-2023        
3               C                   01-02-2023      
4               D                  10-02-2023        
5               E                   11-03-2023                    

calculation example for one employee: -
emp_id        date_of_joining        diff_till_feb_in_months[28/02/2023]        diff_till_march_in_months[31/03/2023]
1                  14-01-2023                              1.5123                                                     2.5315    

output: -
month        AVG_Exp
Feb            1.1075    
March        1.84

For calculating the date difference mentioned above for emp A= 1.5123 which is calulated based on the difference from date_of_joining to 28/02/2023 in days and divide it by 30.4166666667[average days in a month].
I want to create a bar chart with month in x axis and I want to show the average experience of employee month-wise.
and I want to show for the last 3 years. we can have a year slicer so that we can select a year and show data only for that year.
Note:- if the person has joined in Feb and we are calculating average experience in Jan then don't consider that employee as "doj>selected date period"

[ahmedoye]

Try the approach below: If it works for you, kindly mark this as solution so anyone with similar issues can easily find it.

Measure = 
VAR JoiningDate = MAX(date_of_joining)
VAR EndOfCurrMonth = MAX(DateTable[Date])
VAR DaysDifference = MAX(DATEDIFF(JoiningDate, EndOfCurrMonth, DAY), 0)
VAR AverageExperience = DIVIDE(DaysDifference, 30.4166666667, 0)

RETURN IF(AverageExperience = 0, BLANK(), AverageExperience)

[Subhamoy]

@ahmedoye  Thanks for your time!! but this solution didn't work for me!!