Forum Discussion
Creating a report starting from two tables with different layouts
To address the 1st part, Unless the first 3 columns[WBS L1, WBS L2, WBS L3] stays constant, you can use the "unpivot other columns" options in PowerQuery to do the job for you.
As far as the dates, Whatever the date range is from, You can use Power Query to make it start of everymonth or StartofMonth Dax function to create a column.If you still have doubts or questions, Can you provide a Sample file or image on how exactly current structure is?
Thanks,
NG
If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!
Memorable MemberHi
You can unpivot the planned table into similar structure as actual. The table structure will be something like below[you can make the month column as start date using dax or power query]. Make sure to keep the date in actauls to start date of the month as well.
You can create a model using the new planned, actual and a date table using start of the month field in these two tables and perform the calculation you are looking for.
Does this satisfy your requirement? If you have any questions in implementation, Let me know.
If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!
Hello, thank you for your response.
Regarding the planned table, I thought about something similar too, the only problem being the fact that months go from 23/03 to 31/12. Every cost paired with a WBS would sum up to roughly 9000 rows. Did you figure out a way to unpivot the planned table automatically? Furthermore, the table is subject to periodical change, so it is a conversion that needs to be done more than once.
The date in the planned table are at the 1st of each month, while on the actual it tends to be from 20th to 28th. It shouldn't be an issue as long as the level of detail I need for the reports is month by month, is that correct?