Headcount by Function Query

[yaman123]

Hi all, 

A bit of a complicated one!.

I have the below dataset from a sql query. 

What i would like to achieve is the below: 

This is a headcount of employees in each function for a month. E.g from the dataset, if the costctr is 145 and site is HO then this is central technical etc. I have a date table which is used to select the month year. 

But as you can see from the dataset, there can be changes to each of the properties and this creates 2 or more lines for the same employee and the valid from and valid to fields are updated when the change happens.

Is there any way to write a measure to get the graph attached? 

TIA

[v-deddai1-msft]

Hi @yaman123 ,

Would you please show us sample data in excel by onedrive for business?  At present, I cannot correspond to the column in the data format you gave.

And confirm your needs again,  you need two columns: one column is the earliest valid from time by employee， the other one is latest valid to time by employee.

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

Best Regards,

Dedmon Dai

[v-deddai1-msft]

Hi @yaman123 ,

I didn't see function value from your sample data and I can't get accurate reuqirements from your description. Would you please show us detailed sample data ande expected ouput like pbix file by onedrive for business? Then we can help you more correctly.

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

Best Regards,

Dedmon Dai

[yaman123]

Hi @v-deddai1-msft 

I would like to create two columns in the dataset - VALID FROM and VALID TO.

Valid From is earliest date from the line and Valid To is the earliest date from the line.  If you look at Emp 530112, 3 changes have been made to their record 

EMP_NO DATE_OF_EMPLOYMENT DATE_OF_LEAVING LEAVING_CAUSE_TYPE FUNCTION COSTCTR_VALUE COSTCTR_FROM COSTCTR_TO GRADE_VALUE GRADE_FROM GRADE_TO SITE_VALUE VALID FROM VALID TO
300520 01/10/2000 31/12/9999 Commercial 270 03/09/2019 31/12/9999 L4 01/04/2020 31/12/9999 HO 01/04/2020 31/12/9999
300520 01/10/2000 31/12/9999 Commercial 270 03/09/2019 31/12/9999 L5 03/09/2019 31/03/2020 HO 03/09/2019 31/03/2020
300770 28/06/2004 31/12/9999 Membership 602 03/09/2019 31/12/9999 L4 01/01/2021 31/12/9999 HO 01/01/2021 31/12/9999
300770 28/06/2004 31/12/9999 Membership 602 03/09/2019 31/12/9999 L5 03/09/2019 31/12/2020 HO 03/09/2019 31/12/2020
510132 22/04/2014 31/12/9999 LDC Staff 101 03/09/2019 31/12/9999 L6 01/04/2020 31/12/9999 LDC 01/04/2020 31/12/9999
510132 22/04/2014 31/12/9999 LDC Production 101 03/09/2019 31/12/9999 4 03/09/2019 31/03/2020 LDC 03/09/2019 31/03/2020
530112 09/06/2009 31/12/9999 HAV Staff 101 03/09/2019 31/12/9999 L6 01/06/2020 31/12/9999 HAV 01/06/2020 31/12/9999
530112 09/06/2009 31/12/9999 HAV Production 101 03/09/2019 31/12/9999 4 01/11/2019 31/05/2020 HAV 01/11/2019 31/05/2020
530112 09/06/2009 31/12/9999 HAV Production 101 03/09/2019 31/12/9999 3 03/09/2019 31/10/2019 HAV 03/09/2019 31/10/2019

[vanessafvg]

hi are you able to share sample data in text form.

[yaman123]

Hi @vanessafvg 

Sorry if its not the right format. You can use the above attached pic as a reference. 

EMP_NO PROPERTY_CODE1 COSTCTR_VALUE COSTCTR_FROM COSTCTR_TO PROPERTY_CODE2 SITE_VALUE SITE_FROM SITE_TO PROPERTY_CODE3 GRADE_VALUE GRADE_FROM GRADE_TO
300770 COSTCTR 602 03/09/2019 31/12/9999 SITE HO 03/09/2019 31/12/9999 GRADE L5 03/09/2019 31/12/2020
300770 COSTCTR 602 03/09/2019 31/12/9999 SITE HO 03/09/2019 31/12/9999 GRADE L4 01/01/2021 31/12/9999
500491 COSTCTR 250 03/09/2019 10/06/2020 SITE HAV 03/09/2019 31/12/9999 GRADE L5 03/09/2019 31/12/9999
500491 COSTCTR 101 11/06/2020 31/12/9999 SITE HAV 03/09/2019 31/12/9999 GRADE L5 03/09/2019 31/12/9999
500492 COSTCTR 101 03/09/2019 31/12/9999 SITE LDC 03/09/2019 31/12/9999 GRADE L4 03/09/2019 31/12/9999
500493 COSTCTR 145 03/09/2019 31/12/9999 SITE HAV 03/09/2019 31/12/9999 GRADE L5 03/09/2019 31/12/9999
500494 COSTCTR 703 03/09/2019 31/12/9999 SITE HO 03/09/2019 31/12/9999 GRADE L3 03/09/2019 31/12/9999
500494 COSTCTR 703 03/09/2019 31/12/9999 SITE HO 03/09/2019 31/12/9999 GRADE L3 03/09/2019 31/12/9999
500496 COSTCTR 101 17/10/2019 31/12/9999 SITE LDC 17/10/2019 31/12/9999 GRADE 2 17/10/2019 31/12/9999
510001 COSTCTR 101 03/09/2019 31/12/9999 SITE LDC 03/09/2019 31/12/9999 GRADE 2 03/09/2019 31/12/9999