topic Re: Need Help to calculate employee head count for last 12 month in DAX Commands and Tips

Need Help to calculate employee head count for last 12 month

Anonymous — Mon, 02 Nov 2020 15:38:15 GMT

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

Re: Need Help to calculate employee head count for last 12 month

amitchandak — Wed, 28 Oct 2020 16:53:46 GMT

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

Re: Need Help to calculate employee head count for last 12 month

Anonymous — Thu, 29 Oct 2020 03:13:13 GMT

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.

Re: Need Help to calculate employee head count for last 12 month

Anonymous — Sat, 31 Oct 2020 12:22:11 GMT

amitchandak

Furthur assistant, please. Thanks

Re: Need Help to calculate employee head count for last 12 month

Anonymous — Wed, 04 Nov 2020 11:29:35 GMT

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

Re: Need Help to calculate employee head count for last 12 month

Anonymous — Wed, 04 Nov 2020 14:31:37 GMT

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

Re: Need Help to calculate employee head count for last 12 month

Anonymous — Wed, 04 Nov 2020 14:44:27 GMT

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.

Re: Need Help to calculate employee head count for last 12 month

Anonymous — Wed, 04 Nov 2020 14:54:36 GMT

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.

Re: Need Help to calculate employee head count for last 12 month

Anonymous — Wed, 04 Nov 2020 14:56:30 GMT

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.

Re: Need Help to calculate employee head count for last 12 month

Anonymous — Wed, 04 Nov 2020 14:59:27 GMT

I have attached .pbix in my post.

Re: Need Help to calculate employee head count for last 12 month

Anonymous — Wed, 04 Nov 2020 15:01:29 GMT

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.