Forum Discussion
SofG
6 years agoFrequent Visitor
Head count formula not working
Hi, I wish to calcute number of contracts and FTE's. My employee data set looks like this: Person ID Cost Center Role Employment start date ...
v-lili6-msft
6 years agoCommunity Support
HI, SofG
Please try this formula:
measure =
VAR tmpCalendar = ADDCOLUMNS('Dynamic Calendar',"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpBilling = ADDCOLUMNS('People Data',"MonthYearBegin",VALUE(YEAR([Employment start date]) & FORMAT(MONTH([Employment start date]),"0#")),
"MonthYearEnd",IF( [Employment end date]=BLANK(),VALUE(YEAR(MAX('Dynamic Calendar'[Date])) & FORMAT(MONTH(MAX('Dynamic Calendar'[Date])),"0#")) , VALUE(YEAR([Employment end date]) & FORMAT(MONTH([Employment end date]),"0#"))))
VAR tmpTable =
SELECTCOLUMNS(
FILTER(
GENERATE(
tmpBilling,
SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])
),
[MonthYear] >= [MonthYearBegin] &&
[MonthYear] <= [MonthYearEnd]
),
"Persion ID",[Person ID],
"Year",[Year],
"Month",[Month]
)
RETURN COUNTAX(tmpTable,[Persion ID])
If you still have problem, please share your expected output with little sample data.
Best Regards,
Lin
SofG
6 years agoFrequent Visitor
Thank you! When trying this formula it says "Cannot convert value '11/30/202511' of type text to type date. Do you know how to change to type date?