Need help to Calculate Last 12 Month Headcount When we used Relative Slicer.

[adeeln]

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.

Link

[mwegener · Accepted solution]

Hi @adeeln ,

take a look at this.

[adeeln]

Thanks @mwegener your solution is working .Thanks for help.God Bless you

[mwegener]

Hi @adeeln ,

i have tried something with a disconnected table here.

Take a look at it.

[adeeln]

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