Extrapolate/Fill or something?

[Jamez_P]

Hi thanks in advance for any help.

Background:
I am building a project management dashboard with the aim of this piece to accurately show the booked costs and planned costs of current projects. There are two tables a booking table and a planning table and both of these tables work in a similar way with a month, project number, number of hours and a worker ID.

My issue:
The cost table has Worker ID, valid from and cost. The valid from is a period date in the format YYYY0MM - all of the tables use this, I have steps to convert it to a normal date everywhere.
I think I can do a merge to find the costs in the bookings and planning tables but I am not sure how to dynamically expand the costs table out and fill for each month/period to create something that can be merged.
Nice to have: as the projects are opened and the current projects change, the table as a minimum dynamically grows forwards in time based on the project planned furthest into the future.

Example of the cost table

EmployeeNumber	ValidFrom	Hourlyrate
1001	2023009	90
1001	2022007	80
1001	2020010	70
1002	2023008	90
1002	2022007	75

[Anonymous · Accepted solution]

Hi @Jamez_P 

You can refer to the following ways

1.Use merge queries in power query, find the same column among the tables, e.g the [employeeid] column in cost table and [employeeid] column in booking table, then expand it. you can refer to the following link.

Merge queries overview - Power Query | Microsoft Learn

2.If you have the relationship among tables, you can use calculate() function and related() function to find the related value from another table.

RELATED function (DAX) - DAX | Microsoft Learn

If the following above cannot meet your requirement, can you provide some sample data of the tables and the output picture you want?

Best Regards!

Yolo Zhu

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