Actual and Budget with different granularity

[joslaz]

Hey, together


I'm currently facing a small challenge again.I have the following two tables.

TblActuals
[TABLE="width: 500"]
<tbody>[TR]
[TD]Product ID[/TD]
[TD]Group[/TD]
[TD]Quantity (Actual)[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A[/TD]
[TD]100[/TD]
[TD]01.01.2019[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]A[/TD]
[TD]150[/TD]
[TD]02.01.2019[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]B[/TD]
[TD]50[/TD]
[TD]15.01.2019[/TD]
[/TR]
[TR]
[TD]B2[/TD]
[TD]B[/TD]
[TD]150[/TD]
[TD]16.01.2019[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]C[/TD]
[TD]25[/TD]
[TD]08.08.2019[/TD]
[/TR]
</tbody>[/TABLE]


TblBudget
[TABLE="width: 500"]
<tbody>[TR]
[TD]Group[/TD]
[TD]Budget[/TD]
[TD]Month[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]500[/TD]
[TD]January[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]500[/TD]
[TD]February[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]550[/TD]
[TD]March[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]600[/TD]
[TD]April[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]100[/TD]
[TD]May[/TD]
[/TR]
</tbody>[/TABLE]



Now I would somehow connect both table so that I get displayed in a Pivot,
How much have I delivered per group so far (Actual) compared to the daily average for the budget.



In other words, I would have to somehow determine the budget by the number of working days and accumulated the Actuals against the daily budget. How is that possible?


Best Regards
Joshua

 

[sandy666]

try merge these two table in PowerQuery (Get&Transform) by Group with Full Outer option then Expand to columns as you need
create PivotTable from the result