Allocate Monthly Budgets to Weeks using weightage for each week

[Nupur]

Weekly Sales Variation Test.pbix (1.3 MB)

Hi all,

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.
Thanks,

[Melissa]

Hi @Nupur,

First I removed the bidirectional filter between the Date and WeekDate tables.

Next I marked your Date table as a Date table

Added a calculated column to your WeekDate table

and created this measure

When placed in the table visual it produces the same result as the implicit measure. When placed in a table visual with a Date field on the rows it returns the same value for each day in that week.

If you need further assistance please provide a mock up of expected results.
Here’s your file: eDNA Weekly Sales Variation.pbix (1.3 MB)

I hope this is helpful.

[Nupur]

Hi Melissa,

Thank you for the solution.
I did the above changes and applied to my model, however the sales budget value is the same across all weeks of the month rather than allocating it to the weeks based on the weight%.

My model is quite heavy so attached image to show you the results.

Thanks again!

[Melissa]

Hi @Nupur,

Created a measure for monthly budgets

Monthly Budget = 
VAR myWeek = SELECTEDVALUE( 'Date'[Week No.] )
RETURN

CALCULATE( 
    SUMX( Sales_Bdg, Sales_Bdg[Budgeted Sales $] ),
    ALL( 'Date' ),
    TREATAS( VALUES( 'Date'[Month&Year] ), Sales_Bdg[Month&Year] )
) 

and for weekly budget allocation (by the way your weekly % exceeds 100% so budget ends up higher than total budget in your Sales_Bdg table)

Weekly Budget Allocation = 
VAR vTable =
ADDCOLUMNS(
    ADDCOLUMNS(
        VALUES( 'Date'[Week No.] ),
        "Weight", [Weekly %],
        "Budget", [Monthly Budget]
    ),  "Allocation", [Budget] * [Weight]
)
RETURN

    SUMX( vTable, [Allocation] )

.

With this result.

I hope this is helpful.

[EnterpriseDNA]

Hi @Nupur, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

[Nupur]

Hi @Melissa,

I tried your steps mentioned and corrected my percentage allocation as well.
A new issue now! The last week for each month is replicating twice and it takes the allocation two times!
For example: Last week of July should take 6% only while it’s taking 6% and also 21% for last week I have also attached snippet for August where again it’s replicating values for last week twice. Can you please advice on this. This is getting on my head now… Thanks again for your help. Attached formulas used as well.

[Melissa]

Hi @Nupur,

If that is the case we need to rebuilt the Week identifier so it’s unique to each Week in Month and Year. I’d go for a large integer you can built that easily in Power Query, just add a Custom Colum and enter something like:

[Year] * 10000 + [MonthOfYear] * 100 + [WeekOfMonth]

If you need assistance, provide the most up to date version of your sample PBIX and data in XLSX
Thanks.

[Nupur]

Weekly Sales Variation Sample.pbix (3.6 MB)
Hi @Melissa,

Attached latest pbix sample file. I have removed all the other tables so it’s not bulky.
Thanks!

[Melissa]

@Nupur thanks.

I’ll take a look when I get home tonight.

[Melissa]

Hi @Nupur,

This should do it.
Just incorporated a unique week identifier that couldn’t spill over into other months.
[Year] * 10000 + [MonthOfYear] * 100 + [WeekOfMonth]

And applied the same measures over that new dimension.

Here’s your sample file: eDNA Weekly Sales Variation v2.pbix (2.3 MB)
I hope this is helpful.

[EnterpriseDNA]

Hi @Nupur, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

[Nupur]

Hi @Melissa,

Thanks! That works perfectly fine!

Nupur

[Melissa]

Thanks for the feedback and glad I could help