Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SofG
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
Resolver V
Resolver V

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

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!

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.

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

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.

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?

 

 

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.

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.