calculate the actual

[Nadiyah]

Hi 

I'm now developing an HR KPI, and I want to calculate the actual employee from the beginning till the selected date 

I tried to use the below expression and it worked if I selected the past years.

count(distinct{$< Employee_Code -= {"*EA*"}, Date={"<=$(=Date(max([Date]),'MM/DD/YYYY'))"}>}Employee_Code)

what I want when I select any date I want to see the current employee for example 

I choose Oct 2022 the output should be 540 employee,s not the new joiner this month 

I tried to explain please let me know if you need more explanations  

[RafaelBarrios]

hi @Nadiyah 

i guess you are doing a selection using a YearMonth field, so all your data gets reduced to that month & year.

What you will need to do is to especify in your formula to ignore this YearMonth field by:

count(distinct{$< Employee_Code -= {"*EA*"}, YearMonth,Date={"<=$(=Date(max([Date]),'MM/DD/YYYY'))"}>}Employee_Code)

but that is goin to return all years records, if you only need from Jan to Oct 2022 you will need to set a range

count(distinct{$< Employee_Code -= {"*EA*"}, YearMonth,Date={">=$(=date(yearstart(max([Date])),'MM/DD/YYYY'))<=$(=Date(max([Date]),'MM/DD/YYYY'))"}>}Employee_Code)

Hope this helps.

Best,

Help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 🙂

[Nadiyah]

Hi @RafaelBarrios 

thank you for your answer and yes I want to return all the data from the begining 

field YearMonthare is not identified on my calendar, I tried to use these three but not working 

MonthName(TempDate) as MonthYear,
MonthName(Date#([Due Date],'DD-MM-YYY')) as MonthYear,
Year(TempDate) & Num(Month(TempDate), '00') as YearMonth,