Data model pratice - extract and transform tables - SAP ERP

[Anonymous]

Hi,

I want to create a dashbord of the absence of my company and I'm preparing the data model to begin the calculations, but I have some issues that I can't understand why to solve it.

I extract a transaction table of the absence that have the following headers: employee nº, absence type, start date, end date, nº days, nº hours (I have the same employee number in several rows that evidence diferents types of absence over time).

I want to do the calculations based on employee category, HR area, etc., but the same employee can have several categories and HR area over time, and the data that I extract is in the following way:

Employee nº	Category	Start date	End date
1	Administrative	01/01/2020	30/06/2020
1	Manager	01/07/2020	31/08/2020
1 to	Director	01/09/2020

Employee nº	HR area	Start date	End date
1	Area1	01/01/2020	30/06/2020
1	Area2	01/07/2020	31/08/2020
1	Area1	01/09/2020

My goal is that the employee 1 counts for the absence of "Administrative" and "Area1" and in august in "Manager" and "Area2".

1. How can I manage the model and relationships between this tables?
2. The tables above are both transactions tables?

Regards,

Carlos

[Anonymous]

@v-yangliu-msft 

@amitchandak @Ashish_Mathur 

Thank You for your help!

My problem is how to design the data model.

There are some pictures of my data model and tables:

fContractType:

fProfessionalGrp:

In this tables, it is possible to have Employee ID repeated because they can change their professional category and contract type over the time.

The table fEmployeeData have only one row with each EmployeeID.

I'm a beginner in PowerBI, and I have planned do trainning in March, but I want to develop some knowledge until there.

Thanks for yours support.

[Ashish_Mathur]

Hi,

Did my calculated formula work?

[v-yangliu-msft]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated table.

Table = SUMMARIZE('Table1','Table1'[Employee nº],'Table1'[Category],'Table1'[Start date],'Table1'[End date],
"HR area",CALCULATE(MAX('Table2'[HR area]),FILTER('Table2','Table2'[Employee nº]=EARLIER('Table1'[Employee nº])&&'Table2'[Start date]=EARLIER('Table1'[Start date])&&'Table2'[End date]=EARLIER('Table1'[End date]))))

2. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[amitchandak]

@Anonymous , refer if my HR analytics blog or video can help you

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

https://www.youtube.com/watch?v=e6Y-l_JtCq4

[Ashish_Mathur]

Hi,

If the Start date and End Date columns in both datasets match for the same employee, then write this calculated column formula in Table1

=lookupvalue(Table2[HR Area],Table2[Employee Number],Table1[Employee Number],Table2[Start date],Table1[Start date],Table2[End date],Table1[End date])

Hope this helps.