Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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
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:
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
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
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]))
I forgot to mention that correct end date is based on end date modified:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |