topic Re: Average, period end FTE/PAX based on start date and end date employment/contract change in DAX Commands and Tips

Average, period end FTE/PAX based on start date and end date employment/contract change

Anonymous — Thu, 10 Dec 2020 10:26:18 GMT

Hello All!

I'm trying to build visualization of HR data in PowerBI but before I will do this, I'm working on PowerQuery and Data Model using Excel just to be able to talk with HR members on data quality and integrity.

Now I've managed to build model which provide average values in lines for each employee, position, dept. ect. together with time table but when I've applied virtual tables (TAB1,TAB2) to calculate average FTE/PAX data my model slow down dramatically...

I have started with formula for sum of FTE:

sFTE:=CALCULATE(
SUM(Contracts[Full-time equivalent]),
FILTER(VALUES(Contracts[Line applies as of this date]), Contracts[Line applies as of this date]<=MAX('Calendar'[Date])),
FILTER(VALUES(Contracts[Line applies to this date]),OR(Contracts[Line applies to this date]>=MIN('Calendar'[Date]),ISBLANK(Contracts[Line applies to this date])))
)

and for PAX number I've used formula:

sPAX:=CALCULATE(
DISTINCTCOUNT(Contracts[Personnel number]),
FILTER(VALUES(Contracts[Line applies as of this date]), Contracts[Line applies as of this date]<=MAX('Calendar'[Date])),
FILTER(VALUES(Contracts[Line applies to this date]),OR(Contracts[Line applies to this date]>=MIN('Calendar'[Date]),ISBLANK(Contracts[Line applies to this date])))
)

but when applyied formula to calculate average FTE:

FTE:=CALCULATE(
AVERAGEX(SUMMARIZE('Calendar','Calendar'[Date],"TAB1",'Contracts'[NofD]*'Contracts'[sFTE]),[TAB1]),
FILTER(VALUES(Contracts[Line applies as of this date]), Contracts[Line applies as of this date]<=MAX('Calendar'[Date])),
FILTER(VALUES(Contracts[Line applies to this date]),OR(Contracts[Line applies to this date]>=MIN('Calendar'[Date]),ISBLANK(Contracts[Line applies to this date])))
)

and average PAX:

PAX:=CALCULATE(
AVERAGEX(SUMMARIZE('Calendar','Calendar'[Date],"TAB2",'Contracts'[NofD]*'Contracts'[sPAX]),[TAB2]),
FILTER(VALUES(Contracts[Line applies as of this date]), Contracts[Line applies as of this date]<=MAX('Calendar'[Date])),
FILTER(VALUES(Contracts[Line applies to this date]),OR(Contracts[Line applies to this date]>=MIN('Calendar'[Date]),ISBLANK(Contracts[Line applies to this date])))
)

where 'Contracts'[NofD]:

NofD:=CALCULATE(
COUNTROWS('Calendar'),
FILTER(VALUES(Contracts[Line applies as of this date]), Contracts[Line applies as of this date]<=MAX('Calendar'[Date])),
FILTER(VALUES(Contracts[Line applies to this date]),OR(Contracts[Line applies to this date]>=MIN('Calendar'[Date]),ISBLANK(Contracts[Line applies to this date])))
)

my model slowed down...

A slice of the data I'm working on can be found below:

Please notice that it could happen that for one employee FTE can change during the year few times and even month (this is why I need average because sum gives me wrong result when using timetable...).

Personnel number	Line applies as of this date	Line applies to this date	Full-time equivalent
2139	01/04/2020	30/09/2020	1.00
1302	01/07/2019		1.00
1302	07/09/2020		0.00
1736	29/10/2018	11/08/2019	0.40
1386	07/01/2019	31/03/2020	1.00
1386	01/04/2020	31/05/2020	0.83
1386	01/06/2020	30/06/2020	0.52
1386	01/07/2020	31/03/2021	0.50
1386	01/04/2021		1.00
1331	02/08/2017	24/03/2020	1.00
1331	25/03/2020	31/08/2020	0.00
1331	01/09/2020	30/09/2020	0.60
1331	01/10/2020	26/10/2020	0.51
1331	27/10/2020		1.00
2132	09/03/2020	31/03/2020	1.00
2052	17/01/2020	15/03/2020	1.00
2052	16/03/2020	18/05/2020	0.40
1546	15/02/2019	31/03/2020	1.00
1546	01/04/2020	31/05/2020	0.00
1546	01/06/2020	30/06/2020	0.13
1546	01/07/2020	31/03/2021	0.40
1546	01/04/2021		1.00
1979	03/11/2019	24/03/2020	1.00
1979	25/03/2020	31/08/2020	0.00
1979	01/09/2020	30/09/2020	0.60
1979	01/10/2020	26/10/2020	0.51
1979	27/10/2020		1.00

Maybe anyone of U could help me to eliminate this slowing down formula and help me to achieve data at the end of each period (month end, year-end ect.).

Thank U in advance!

BR | BMR

Re: Average, period end FTE/PAX based on start date and end date employment/contract change

daxer-almighty — Thu, 10 Dec 2020 16:39:48 GMT

A piece of good advice: You should never, ever use SUMMARIZE to do anything more than just get the distinct combination of rows. It's too much to talk about and I don't have the time and space here to enlarge upon this very important issue. If you go to www.sqlbi.com, you will find an article by Aberto Ferrari and Marco Russo that discusses at length why it's a VERY, VERY BAD IDEA. This is one source of your problem but I guess this is not the only one. But you at least know where to start....

Re: Average, period end FTE/PAX based on start date and end date employment/contract change

Anonymous — Sun, 13 Dec 2020 10:46:24 GMT

Thanks daxer-almighty for advice but probably when I would be able to do this correctly I wouldn't raise this question here... Of course I will do my best to get right direction but for now other proposition for solution more than welcome 🙂

BR | BMR

Re: Average, period end FTE/PAX based on start date and end date employment/contract change

Stachu — Sun, 13 Dec 2020 15:00:59 GMT

is there any active join between 'Contracts' and 'Calendar'? The syntax you posted is all for calculated measures not columns, correct?
Do you get the same result for [FTE] with the syntax below? Is the performance better?

FTE2 = CALCULATE ( AVERAGEX ( VALUES ( 'Calendar'[Date] ), [NofD] * [sFTE] ), FILTER ( ALLSELECTED ( 'Contracts'[Line applies as of this date], 'Contracts'[Line applies to this date] ), Contracts[Line applies as of this date] <= MAX ( 'Calendar'[Date] ) && ( Contracts[Line applies to this date] >= MIN ( 'Calendar'[Date] ) || ISBLANK ( Contracts[Line applies to this date] ) ) ) )

Re: Average, period end FTE/PAX based on start date and end date employment/contract change

Anonymous — Wed, 16 Dec 2020 00:17:14 GMT

Hi Stachu!

Thank you for taking a look and trying to help me with my formula 🙂

Regarding your question - yes I had inactive relationships between dates column (Line applies as of this date, Line applies to this date) and my syntax related to calculated measures.

Now your syntax is working in PowerBI but I have no idea why in PowerPivot the formula returns error in below area:

ALLSELECTED (

'Contracts'[Line applies as of this date],

'Contracts'[Line applies to this date]

For me it is still necessary to use excel as a spreadsheet in communication with HR so hope this problem you will be able to help me with as well.

Re: Average, period end FTE/PAX based on start date and end date employment/contract change

Stachu — Wed, 16 Dec 2020 10:24:36 GMT

what error does it return? could it be due to e.g. not consistent column names, e.g. extra space somewhere?

Re: Average, period end FTE/PAX based on start date and end date employment/contract change

Anonymous — Thu, 17 Dec 2020 08:33:41 GMT

In Power BI no any syntax error:

but in PowerPivot

Any idea?