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.
Hello Experts,
I need to plot a trend line where I need to show the last 12-month employee headcount base on the selection (Relative slicer max date). I've HR fact table and date dimension joined based on employee date of joining.
I've created DAX to calculate HC (Headcount) based on date selection(Max date from a relative slicer), it's working fine.
HC =
VAR MaxDate =
MAX ( 'DimDate'[Date] )
VAR EmpCnt =
CALCULATE (
COUNTROWS (
CALCULATETABLE ( 'HR', 'HR'[DateOfJoining] <= MaxDate, ALL ( 'DimDate' ) )
),
(
ISBLANK ( 'HR'[TerminationDate] )
|| 'HR'[TerminationDate] > MaxDate
)
)
RETURN
IF ( ISBLANK ( EmpCnt ), 0, EmpCnt )
In the same report, I need to show the same measure i.e. HC but for the last 12-months. For example, if I choose to show the last two calendar years from relative slicer then Dax should pick the max date from slicer and show the last 12 months from that max date.
I have Used following Dax its working fine On Last 2 Or Years but not Working On Last 2 Or 3 Month,Or Last 2 OR 3 Days Or Last 2 OR 3 Weeks,Or Next Months,Days etc.
HC =
VAR _Max1 =
MAXX ( allselected('DimDate'), 'DimDate'[Date] )
VAR _Max = date(year(_max1), month(_max1)-12, day(_max1))
VAR MaxDate =
MAX ( 'DimDate'[Date] )
VAR EmpCnt =
CALCULATE (
COUNTROWS (
CALCULATETABLE ( 'HR', 'HR'[DateOfJoining] <= MaxDate, ALL ( 'DimDate' ) )
),
(
ISBLANK ( 'HR'[TerminationDate] )
|| 'HR'[TerminationDate] > MaxDate
)
)
RETURN
IF ( ISBLANK ( EmpCnt ) , 0, if(max('DimDate'[Date])>=_Max, EmpCnt, blank() )).
Here is the link PBIX file.
Solved! Go to Solution.
Hi @mwegener ,
Great Work, your solution is working fine for "Last 12-month Headcount" based on Date selection from the "dimdateSlicer" table.
But when I go to create "Headcount by PositionType " then results are not correct.
is it possible to get results without creating a separate disconnected date table because I need to use the same date slicer for other dimensions like ( by "position type", "office", "department"), etc?
Can you please see this issue?
Thanks Regards,
Adeel
User | Count |
---|---|
96 | |
86 | |
78 | |
72 | |
67 |
User | Count |
---|---|
110 | |
104 | |
84 | |
65 | |
63 |