Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I would like to calculate a FTE (full time equivalence), a measure to calculate the headcount :
HC-Headcount = IF ( [Data Date] >= MIN ( 'Calendar'[Date] ), CALCULATE ( DISTINCTCOUNT ( 'xa BuildUp To Gross'[Payroll nb] ), FILTER ( 'xa BuildUp To Gross', COUNTROWS ( FILTER ( RELATEDTABLE ( xADM00_General), 'xa BuildUp To Gross'[Administrative start date] <= MAX ( Calendar[Date] ) && ( ISBLANK ( 'xa BuildUp To Gross'[Administrative End Date]) || 'xa BuildUp To Gross'[Administrative End Date] >= MAX ( Calendar[Date] ) ) ) ) > 0 ) ) )
I’ve tried to use the same formula of headcount for FTE just changing the line : countrows for sum, sumx, but isnt work.
for the headcount, each one has a FTE (full time equivalence), expressed in the column C.
Solved! Go to Solution.
I've found the solucution :
1. I've create a inative relation between the calendar Date and the reference date in the database
2. and use this measure:
HC - FTE = CALCULATE (
SUM ('xa BuildUp To Gross'[FTE]), FILTER ( 'xa BuildUp To Gross', 'xa BuildUp To Gross'[Administrative start date] <= MAX ( Calendar[Date] ) && ( ISBLANK ( 'xa BuildUp To Gross'[Administrative End Date]) || 'xa BuildUp To Gross'[Administrative End Date] >= MAX ( Calendar[Date] ) ) ), USERELATIONSHIP('xa BuildUp To Gross'[Reference],'Calendar'[Date]))
It's tough to tell exactly what you are trying to do here but I am guessing you want the FTE for a point in time taking into considering the start and end dates of employees? Have a look at my new measure in the Quick Measures gallery, made possible by @Phil_Seamark's fantastic new book, Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence. The Quick Measure link is here:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147
Should be easily adaptable to your situation.
HI @Greg_Deckler, Thanks for your qucik reply.
It's almost what I'm looking for, but your measure counts rows, and I'd like to sum the rows, I'tll try to be more clear:
I have a payroll file that I've received every month, with some columns:
- Reference: respective month and year
- Payroll number : number of register of the employee
- FTE : Full time equivalence
- Work pattern: describing if the employee is part time or full time
- Administrative start date : Hiring date, the date that starts the contract with the company
- Administrative End date: Leving date, the date that finished the contract with the company
In the powerbi database every month the employees record repeats itself until the month he/she left the company (If active the administrative End date is blank). Example:
REFERENCE PAYROLL NB FTE WORK PATTERN ADM START DATE ADM END DATE
JAN 2017 001 0.6 Part time 10-01-2017
JAN 2017 002 0.4 Part time 20-01-2017
FEB 2017 001 0.6 Part time 10-01-2017
FEB 2017 002 0.4 Part time 20-01-2017
FEB 2017 003 1 Full time 01-02-2017
MAR 2017 001 1 Full time 10-01-2017
MAR 2017 002 0.4 Part time 20-01-2017
MAR 2017 003 1 Full time 01-02-2017
...
MAR 2018 001 1 Full time 10-01-2017
MAR 2018 002 0.4 Part time 20-01-2017
MAR 2018 003 1 Full time 01-02-2017
The troube/issue is:
As headcount each month employee should counted as 1 ( it's sorted with the measure HC - Headcount as i've written above), but as FTE i need to sum the cvalues for FTE tto have the result:
JAN 2017 Headcount is 2 and FTE is 1
FEB 2017 Headcount is 3 and FTE is 2
MAR 2017 Headcount is 3 and FTE is 2.4
MAR 2018 Headcount is 3 and FTE 2.4
As you can see depending on the month the FTE value can change, and i need to sum for each month the respective values.
I have other tables with relationship to this one to use as filter, and a calendar table with no relation with the others.
I've met a alternative way to make the sum, but I'm struglling to insert the filter for sum actives ones:
FTE = IF ( [Data Date] >= MIN ( 'Calendar'[Date] ), CALCULATE(Sum('xa BuildUp To Gross'[FTE]),USERELATIONSHIP('xa BuildUp To Gross'[Reference],'Calendar'[Date])))
I've found the solucution :
1. I've create a inative relation between the calendar Date and the reference date in the database
2. and use this measure:
HC - FTE = CALCULATE (
SUM ('xa BuildUp To Gross'[FTE]), FILTER ( 'xa BuildUp To Gross', 'xa BuildUp To Gross'[Administrative start date] <= MAX ( Calendar[Date] ) && ( ISBLANK ( 'xa BuildUp To Gross'[Administrative End Date]) || 'xa BuildUp To Gross'[Administrative End Date] >= MAX ( Calendar[Date] ) ) ), USERELATIONSHIP('xa BuildUp To Gross'[Reference],'Calendar'[Date]))
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |