Weekly Sales Variation Test.pbix (1.3 MB)
I have a power bi file where I have the Sales Budget table where the sales is allocated monthly on the last day of the month. I now need to allocate these to weekly level using the percentage allocation for each week based on previous FY. For this, I created a weekly time table which has date, month and weight% for each week of the month.
I tried SUMX and other formulas but all these give the weekly allocation only for the last week of the month and not across all the weeks.
My aim is to then create a variation analysis between sales budget and sales actual. My sales actual are at a daily level but I will have variation analysis at daily, weekly and monthly level.
Can someone please guide me on the correct methodology to use here?
I tried Sam’s logic for daily allocation and that works completely fine.
Attached pbix file as well.