Create a table with new rows

[jlarques]

Dear community,

I have another challenge to overcome.

I have two tables: 

• Payroll table with pay date of each employee
• Project table with start and end date and  % project attribution of each employee

I want to create a new table that includes for each month:

• Start date
• End date
• Employee
• Project
• %Project attribution

This table is created based on start and end date of date's project. For instance, first project begins in 1st January and end in 8th of August, so the table has to have as many rows as months are included between the start and end dates, only for this project. Verify each project and do the same for each month including %project attribution. I attached an image to try to clarify all the details.

The thing is that an employee can be working on several projects in the same month and the idea is calculate the %project attribution for each month and each project, based on project table.

Thanks in advance for your help.

José Luis

[AlienSx]

@jlarques seems like project table contains inconsistent data. Why nothing happens after Aug 20th? Why total project allocation <> 100% after Aug 20th?

[jlarques]

Hi @AlienSx ,

because project finish in this date. Other projects have another dates.

Thanks,

[amitchandak]

@jlarques , You can do have additional row using

Tables way
https://amitchandak.mediumcom/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

Measure way

Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

Better the HR Way

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

[jlarques]

Hi @amitchandak ,

apologies for my delay's answer.

I watched your videos and I understood you can do it, but I don't how to add rows with the specific problem that I have, because you're using measures and it's complicated for me translate it to a tables.

Thanks for your help.

[jlarques]

@amitchandak 

thanks for your answer. 

Let me review all your information and I'll tell you if I can do it or not. 

Thanks again for your help.