cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SofG Frequent Visitor
Frequent 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         Employment end date               Work degree             PersonCCRoleWD               PersonCCRole

 

I have a separate calendar table which I try to connect with the employee data thorugh formulas.

 

I started of by using the forumlas below because I only want to calculate the rows when people have actually changed role/cost center/work degree. (One employee can be counted multiple times in our system, and I only want to count them once with the help of this "code").

 

Correct Start date = CALCULATE(MIN('People Data'[Employment start date]);

ALLEXCEPT( 'People Data'; 'People Data'[Person CC Role WD]))

 

Correct end date = CALCULATE(MAX('People Data'[End date modified]);

ALLEXCEPT( 'People Data'; 'People Data'[Person CC Role WD]))

 

To count number of contracts, I used this formula:

 

Contracts =

CALCULATE (

DISTINCTCOUNT ('People Data'[Person CC Role]);

FILTER (

'People Data';

'People Data'[Correct Start date]<=MAX('Dynamic Calendar Table'[Date])

&& 'People Data'[Correct end date]>MAX( ('Dynamic Calendar Table'[Date])

)))

 

Some how I do not get the right number of contracts with the above formulas.

 

When i try to calculate FTE's I use this formula:

 

FTE =

CALCULATE(

SUMX (DISTINCT('People Data'[Person CC Role]);[Max Work degree alt])

;FILTER (

'People Data';

'People Data'[Correct Start date]<=MAX('Dynamic Calendar Table'[Date])

&& 'People Data'[Correct end date]>MAX( ('Dynamic Calendar Table'[Date] )

)))

 

Where Max work degree is based on:

Max Work degree alt = CALCULATE ( [Max Work Degree];

FILTER('People Data';

Max('People Data'[Employment start date])))

 

 

max work degree = calculate(max('People Data'[Work Degree]);allexcept('People Data';'People Data'[Person CC Role]))

 

 

 I hope this was somewhat clear.. Can anyone help me? MY PBI file is depening on the head count to be correct. Thanks in advance!

 

Kinds regards

9 REPLIES 9
vik0810 Member
Member

Re: Head count formula not working

Hi,

 

your criterium in the FILTER formula is not quite correct. You have

 

People Data'[Correct end date] > MAX( ('Dynamic Calendar Table'[Date])

and it should be 

 

People Data'[Correct end date] >= MIN( ('Dynamic Calendar Table'[Date])

say, If your date filter is 01.01.2017 - 31.12.2017 and someone quits on 30.06, this person won't be included in your measure.

SofG Frequent Visitor
Frequent Visitor

Re: Head count formula not working

Thank you Vik0810!

 

That helped! However the number is still way too low. For FY19 Q2 it says about 800 people less than my filtering in Excel.

 

When I filter further down to month level, the number decrease even more. As an example: My excel say around 4000 for Q2, PBI says 3000 for q2 and 1000 for June. I would like for FY2019 and all quarters to calcuate the last date in that period. It should not differ that much from month to month... We are constantly around 4000 employees. Do you have any idea why it still differs so much?

 

Thanks!

vik0810 Member
Member

Re: Head count formula not working

I'm not sure, but I think you are not considering people with empty employment end date, so may be your criterium should be

 

OR(People Data'[Correct end date] >= MIN( ('Dynamic Calendar Table'[Date]), ISBLANK(People Data'[Correct end date]))
SofG Frequent Visitor
Frequent Visitor

Re: Head count formula not working

I forgot to mention that correct end date is based on end date modified:

 

End date modified  IF(ISBLANK('People Data'[Employment end date]);TODAY()+1;'People Data'[Employment end date])
 
Which I assume has the same function?
 
Thank you so much for helping me! Unfortunately the numbers are still wrong.
Community Support Team
Community Support Team

Re: Head count formula not working

hi, @SofG 

Here is a same post for you refer to:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

If you still have problem, please share your sample pbix and expected output.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SofG Frequent Visitor
Frequent Visitor

Re: Head count formula not working

Thank you!

 

Unfortunately I wasn't able to get "Periodic billing" code to work. Maybe it is because I already have a calendar table which I use to filter multiple other tables?

 

I created a sample of some modified data. I want to measure how many employees we are at a specific time. If I filter 2019, I want it to calculate to the latest date within that filter.

I would be really greatful if you could take a look at this data:

Sampla data in sharepoint

 

Thank you in advance!

 

Kind regards

Community Support Team
Community Support Team

Re: Head count formula not working

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SofG Frequent Visitor
Frequent Visitor

Re: Head count formula not working

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?

 

 

Community Support Team
Community Support Team

Re: Head count formula not working

hi, @SofG 

'11/30/202511' is not the format of date, and I don't find it in the sample pbix file.

what is "11" in this string 11/30/202511'?

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 403 members 3,349 guests
Please welcome our newest community members: