Converting annual budget to monthly budget based on previous year's sales data.

[ED864]

Hello,

I have a list of sales budgets (targets) which are written by brand per year. I would like to break these down into monthly budgets based on the previous year's sales data (not jsut divided by 12) to account for seasonal trends.

This seems like it should be straightforward but I've been going round in cicrcles for ages and haven't gotten anywhere.

Here is some dummy data:

The columns in black already exist in my model. The columns in red are the steps I would like to take.

Step 1: Calculate the total sales for the previous year based on brand.

Step 2: Calculate the total sales for the previous year based on brand and split by month.

Step 3: Divide the Monthly Sales PY by the Total Sales PY to get the percentage split of sales for each month per brand.

Step 4: Multiply the Total Sales Budget by the % Total Sales PY to get the Monthly Budget for the current year by brand. 

I'm not sure if this can all be done in one Measure or maybe just adding calculated columns to my table would be better.

Here is my model:

Thanks in advance for any help and advice.

[isjoycewang · Accepted solution]

Hi @ED864 ,

Attached the demo file.

You can create several columns or you can also write them all in one single column.

Monthly Bugdet = 
VAR _brand = [Brand]
VAR _month = [Month]
VAR _total_sales_PY = CALCULATE(SUM(Sales[Line Revenue]), FILTER(ALL(Sales), Sales[Brand] = _brand))
VAR _monthly_sales_PY = CALCULATE(SUM(Sales[Line Revenue]), FILTER(ALL(Sales), Sales[Brand] = _brand && Sales[Month] = _month))
RETURN
[Annual Sales Budget]* (DIVIDE( _monthly_sales_PY, _total_sales_PY))
// bugdet * monthly_sale%_PY

Feel free to let me know if any questions. Thanks.

Best Regards,

Joyce

[ED864]

That works great! Nice and simple too! Thanks very much.

[sanalytics]

@ED864 

How do you calcuate previous year's total sales.
I dont see any 2023 data in your shared screenshot.
It will be better if you share a dummay data with your pbix file so that we can try from our end.

Regards

sanalytics

[ED864]

@sanalytics 

Please see here a dropbox link with some dummy data:
https://www.dropbox.com/scl/fi/olb4a2l4pcttcvyzb76ec/Dummy-Data.pbix?rlkey=2c5vfm9s096y43kkhf877uk58... 

The previous year's sales data will come from the sales table which includes individual transactions.

Let me know if you need anything else!

Thanks