Create phasing table from campaign forecasts

[Mrdavew]

Hi all. Hope somebody can steer me in the right direction here.

I have data containing product sales forecasts for multiple promotional campaigns. And I need to create a calendarised view of the forecast sales each day.

Nominations table contains:

Product, campaign, start date, end date, total sales volume

I want to apply a phasing to every product, that might be left biased, right biased or flat. Or it might be a completely custom phasing for each campaign.

The output would be enabled me to see the total sales per day / week for all products / campaigns.

Does that make sense to anyone out there?

[Anonymous · Accepted solution]

Hi @Mrdavew ,

Agree with @PwerQueryKees 's approach and expand on it:

1. First create a new table:

Calendar = CALENDAR(MIN('Table'[Start Date]),MAX('Table'[End Date]))

And the relationship:

2. Then pelase create a new measure like:

Measure = 
VAR __cur_date = SELECTEDVALUE('Calendar'[Date])
VAR __result = CALCULATE(SUM('Table'[Daily Sales]), 'Table'[Start Date]<=__cur_date && 'Table'[End Date]>=__cur_date)
RETURN
__result

3. Next please create a line chart and open forecast:

Use the Analytics pane in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Mrdavew]

Thank you so much. This has worked beautifully.  Your extra level of detail really helped. 

This was my first stage to solve for a flat daily forecast, in reality the forecast is biased left or right. i.e customers rush in and buy as soon as the promotion launches, or they buy towards the end as they see its about to finish. So now I need to figure that out!

[PwerQueryKees]

If I understand you correctly... You want

• For each product
• For each date 
• The total predicted average sales
• For all campaigns active on that day?

That would be doable... 

I would:

• Add a column to the nominations table with the average daily sales for the campaign/product combination 
• Create a table with all dates
• Match each date with all active nominations for that date
• Groep by date and pruct to sum all sales

I hope this enough direction for you...