Headcount for Previous Month and March of every year

[GJ217]

Hi,

I have the following DAX measure which calculates Headcount for each month based on a page filter for Dates in the last 12 months

Headcount = CALCULATE(DISTINCTCOUNT(PersonDetails[PERSON NUMBER]),
FILTER(VALUES(PersonDetails[WORK RELATIONSHIP START DATE]), PersonDetails[WORK RELATIONSHIP START DATE] <=MAX(DimDates[Date])),
FILTER(VALUES(PersonDetails[TERMINATION DATE]), OR(PersonDetails[TERMINATION DATE] >=MAX(DimDates[Date]),ISBLANK(PersonDetails[TERMINATION DATE]))))+0

I would like to get the Headcount as at the 31st March in one measureand the Headcount for the end of the previous month in another measure without the use of filters and slicers.

I need to write something similar to the payroll DAX measure below but for Headcount as above, can anyone please show me how this can be written in DAX without filters or slicer selections?

May EmployeeTotal = CALCULATE([Employees_Total],

       MONTH('Payroll'[Regular Date Earned]) = MONTH(TODAY())-2,

       YEAR('Payroll'[Regular Date Earned])= YEAR(TODAY()))

[amitchandak]

@GJ217 , refer if the attached file can help. there are previous period formula

[GJ217]

Hi @amitchandak 

Thank you, how do I get the Salary Headcount for 31 Mar 23 and then still get it for 31Mar 23 when we move into 2024 next year with the DAX below?

Salary Headcount Prev Month = 
var _min_date = MINX(ALL(DimDates),DimDates[Date])
var _Expression = IF(ISFILTERED(DimDates[Month & Year]),MAXX(DimDates, ENDOFMONTH(DATEADD(DimDates[Date],-1,MONTH))),MAXX(DimDates,DATEADD(DimDates[Date],-1,YEAR)))
RETURN
CALCULATE(COUNTROWS(SalaryHistory),
FILTER(VALUES(SalaryHistory[SALARY CHANGE DATE]), SalaryHistory[SALARY CHANGE DATE]<=MAX(DimDates[Date])),
FILTER(VALUES(SalaryHistory[SALARY END DATE]), SalaryHistory[SALARY END DATE] >=MAX(DimDates[Date])))+0

Can you please show me?