Assistance with Time intelligence functions

[VaibhaviShah_9]

@Time-intelligence 
I need to calculate active headcount at end of any given month chosen dynamically by the user.

I calculate basis - LWD is less than or equal to the month end date and date of Joing should be less than or equal to that month 

for the sample format attached,
Assuming this data was as on 01-May-2023
Active count by end of April month is 7
Active count by end of March month is 11

Can someone guide with the related DAX queries plz 

[rajendraongole1]

Hi @VaibhaviShah_9 - Hope you already created a seperate date table in your model. give the relationship with Date table to model of employee table date.

you can try the below dax formuale to get the active head count 

ActiveHeadcount =
VAR SelectedMonthEnd = MAX('DateTable'[MonthEnd])
RETURN
CALCULATE(
COUNTROWS('EmployeeData'),
'EmployeeData'[DateOfJoining] <= SelectedMonthEnd,
'EmployeeData'[LastWorkingDate] > SelectedMonthEnd || ISBLANK('EmployeeData'[LastWorkingDate])
)

 you can see the Active count for each of the month end.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!