Forum Discussion

VaibhaviShah_9's avatar
VaibhaviShah_9
New Member
2 years ago

Assistance with Time intelligence functions

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 

 

 

Need Help

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    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!!