Need Help to calculate employee head count for last 12 month

[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.  

Here is the link PBIX file.

Link

[Anonymous]

And I tell you that I can't open and see it because I'm at work and my Company does not allow to view such things.

[Anonymous]

It might be that you want something different than the algorithm I gave you but then you have to be very specific about what it is you want.

[Anonymous]

Sorry, but what do you mean by "not working"? The code takes the last visible date in 'DimDate' (this must be marked as a date table in the model and you have to use this table in the visual, not any other date field from any other table), checks if there are enough days to move back 12 months from the last visible date in the current context, moves back 12 months if possible and calculates the number of rows in 'HR' which are filtered by these 12 months. So, the code does exactly what you want. I can't see your pbix becasue I can't do it at work due to policies.

[adeeln]

I have attached .pbix in my post.

[Anonymous]

// Assumption:
// The calendar, DimDate, starts
// on 1 Jan of some year.

[HC 12M] =
VAR __lastVisibleDate =
    MAX( 'DimDate'[Date] )
var __veryFirstYearInCalendar =
    CALCULATE(
        min( 'DimDate'[Year] ), // Year must be an int
        all( 'DimDate' )
    )
// This is the minimum date after or on which
// you can calculate the head count. If the date
// is before this day, there are not enough days
// to go back (you have to have 1 full year).
var __firstDay =
    date( __veryFirstYearInCalendar, 12, 31 )
VAR __employeeCount =
    if( __lastVisibleDate >= __firstDay,
        0 + CALCULATE(
            COUNTROWS( 'HR'),
            OR(
                ISBLANK( 'HR'[TerminationDate] ),
                'HR'[TerminationDate] > __lastVisibleDate
            ),
            DATESINPERIOD(
                'DimDate'[Date],
                __lastVisibleDate,
                -1,
                year
            )
        )
    )
RETURN
    __employeeCount
    
// Please note that this measure distinguishes between
// 2 cases:
// 1) If there are enough days to calculate
// the measure but the count is 0, then 0 is returned.
// 2) If there are not enough days to calculate
// the measure, BLANK is returned.

[adeeln]

@Anonymous .I have used your measure but this not working on relative Slicer.

I need last 12 month HC.Whatever we select from Slicer i.e last 2 days,3 days ,last 3 years or last 2 months.Next month.or year etc

I have attached .pbix file please download & sugguest me solution.

[adeeln]

Need Help to calculate employee head count for last 12 month.Please help.

[adeeln]

@amitchandak 

Furthur assistant, please. Thanks

[amitchandak]

@adeeln , Try like

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() ))

[adeeln]

@amitchandak This DAX is Working fine On the Selection Of Last 2 Or 3 years.But this DAX is not Working On the Selection Of "Last 2 Month", "Last 3 month", "Last 2 Week " ,"Last 3 Weeks"  , "Last  2 Days " and On  "This Days", "this Month" etc.