Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Team,
I am trying to create a measure to show active employees for 2018, 2019, 2020, 2021, and 2022 based on hire date and termination date. I will have a slicer for the years.
Active employees for 2018 = 8
Active employees for 2019 = 8
Active employees for 2020 = 8
Active employees for 2021 = 10
Active employees for 2022 = 15
Thanks for all your help.
Solved! Go to Solution.
This solution uses a disconnected Years table (one row per year).
Measure:
Active Employees =
VAR vCurYear =
SELECTEDVALUE ( Years[Year] )
VAR vResult =
SUMX (
Table1,
VAR vHireYear =
YEAR ( Table1[date_of_hire] )
VAR vTermYear =
IF (
ISBLANK ( Table1[date_of_termination] ),
9999,
YEAR ( Table1[date_of_termination] )
)
RETURN
IF ( vCurYear >= vHireYear && vCurYear <= vTermYear, 1 )
)
RETURN
vResult
Use the Years[Year] column in a visual. If you want the Year slicer to be based on your date table, the solution below shows how to achieve this. The concept is to create a clone of the date table, and use DAX to control the visual filtering.
https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/
Proud to be a Super User!
This solution uses a disconnected Years table (one row per year).
Measure:
Active Employees =
VAR vCurYear =
SELECTEDVALUE ( Years[Year] )
VAR vResult =
SUMX (
Table1,
VAR vHireYear =
YEAR ( Table1[date_of_hire] )
VAR vTermYear =
IF (
ISBLANK ( Table1[date_of_termination] ),
9999,
YEAR ( Table1[date_of_termination] )
)
RETURN
IF ( vCurYear >= vHireYear && vCurYear <= vTermYear, 1 )
)
RETURN
vResult
Use the Years[Year] column in a visual. If you want the Year slicer to be based on your date table, the solution below shows how to achieve this. The concept is to create a clone of the date table, and use DAX to control the visual filtering.
https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/
Proud to be a Super User!
Hello Sir,
Really appreciate your help in solving my problem. The DAX measure worked great.
Again thanks for your help
Glad to hear that worked!
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |