Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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"
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)