How to calculate sales forecast based on old and new sales price

[Anonymous]

I have a forecast for my items. Item one has a forecast for period 1 through period 12. The sales price is $15 for periods 1-7 and a new sales price, $13.50, goes in to effect period 8-12.

How do I calculate total sales based on the sales prices in the appropriate period?

[dearwatson]

Hi a_mayes

basically speaking you will need to build a table which for any given date and item gives you the forecast and the price.

e.g.

Date | Forecast | Price | Item

then create a SUMX iterator that calculates the total over this table.

if you supply some sample data I will show you how to calculate this with your structure.

Cheers

Greg

[Anonymous]

Thanks for the response @dearwatson . We use a 4-4-5 financial calendar so I have a date table setup instead of using PBI's. Below is how my table looks (The item name and sales price are a part of the table but I could not get it in to this view all together. I unpivot the 12 columns so that the column headers are now one column of dates.

Item 1 $19.09

Item 2 $36.13

Item 3 $18.17

After unpivoting:

Item Name   Sales Price   Forecast      date

Item 1              19.08          192          1/1/2019

Item 1              19.08          96            1/27/2019

Item 1              19.08          96            2/24/2019

Item 1              19.08          96            3/31/2019

Item 1              19.08          192          4/28/2019

etc.

Would I be able to accomplish this by setting up a different table with only sales prices and then unpivoting similar to above to get the sales price for the item in to the same column with another column for dates?

Thanks,

Andrew

[Anonymous · Accepted solution]

@dearwatson I was able to solve! I need to create a sales table and pass the item master and calendar reference through that table and then create a unique key in the sales price table and the forecast to create a 1 to many (technically it could have a 1:1 and cross filter both, but I only want the forecast to change not the other way around). 

Thanks!

Andrew

[v-juanli-msft]

Hi @Anonymous 

You could forecast sales with Power BI, please learn how to work:

Data Forecasting and Analytics with Power BI Desktop

Forecasting in Power BI

How to forecast sales with Power BI 

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.