Daily Forecast Allocation Weighted by Day Of Week

ZENMarkD · October 5, 2020, 4:54am

Hi Everyone,
Hoping for some help here. I have generated a daily forecast allocation amount based on a monthly forecast. What I would like to do is weight this depending on the actual day of the week, e.g. if Thursdays have a higher percentage of the sales then I want the daily forecast to adjust accordingly.
If I could create the weights dynamically based on historical daily sales, then great. Otherwise I’m happy to just use a simple table with Day Of Week and the % allocated to that day.

Currently here is where my Forecast Allocation formula is at:

Forecast Allocation =

VAR MonthDays = CALCULATE( COUNTROWS( Calendar_Lookup ), ALL( Calendar_Lookup ), VALUES( Calendar_Lookup[Month & Year] ) )

VAR DailyFCST = CALCULATE( [Total FCST $], ALL( Calendar_Lookup ), VALUES( Calendar_Lookup[Month & Year] ) )

return

IF( ISFILTERED( Calendar_Lookup[Date] ),

DIVIDE( DailyFCST, MonthDays, 0 ),

[Total FCST $] )

This produces the daily number shown in the sample data below. I now need to weight that figure.

I have attached some sample data and the daily weight table. Any help would be immensely appreciated.
Thanks in advance
Mark

sampleData.csv (1.7 KB)

DailyWeights.csv (104 Bytes)

ZENMarkD · October 5, 2020, 9:24am

Hi again,

An update: I have made some progress, in that I have managed to apply the weighting, calculate a weekly forecast, then multiply that by the number of weeks in that month.
Cumulatively I get a similar result, but not the same. Is this just a mathematical issue or something else?
Here is the formula:
VAR MonthDays = CALCULATE( COUNTROWS( Calendar_Lookup ), ALL( Calendar_Lookup ), VALUES( Calendar_Lookup[Month & Year] ) )
VAR DailyFCST = CALCULATE( [Total FCST ], ALL( Calendar_Lookup ), VALUES( Calendar_Lookup[Month & Year] ) ) VAR WeeksInMonth = MonthDays / 7 VAR DailyWeight = CALCULATE(AVERAGE('Daily Weights'[Weight %] ), TREATAS( VALUES(Calendar_Lookup[Day Name] ), 'Daily Weights'[Day of Week] )) VAR WeeklyFCST = (DailyFCST * DailyWeight) * 7 return IF( ISFILTERED( Calendar_Lookup[Date] ), DIVIDE((WeeklyFCST * WeeksInMonth ) * DIVIDE( 1, MonthDays, 0 ), WeeksInMonth), [Total FCST ] )

The attached file shows the result I get. Here you can see the difference between the monthly forecast total and the cumulative total, a significant difference.
I am now officially stumped!

sampleData2.csv (1.9 KB)

Greg · October 5, 2020, 12:16pm

Hi @ZENMarkD. I think I can get a result using simple measures and lookup value. Is something like this what you’re looking for?

Hope it helps.
Greg
eDNA Forum - Daily Forecast Allocation Weighted by Day of Week.pbix (71.9 KB)

ZENMarkD · October 5, 2020, 9:18pm

Thanks Greg, but what I need to do is apply the weighting to the monthly forecast figure (Total FCST $). From there I will generate a cumulative forecast for the month so I can compare it to sales.

Apologies if I confused things by including sales in the sample data.

Thanks for including the link to the extended date table though. I’m going to update my ones to this as it’s very useful.

Greg · October 5, 2020, 9:24pm

Thanks @ZENMarkD. Good luck. If you would like further assistance, please post your work-in-progress PBIX and a mockup or Excel spreadsheet showing your desired outcome in order to help forum members visualize your issue.
Greg

EnterpriseDNA · October 6, 2020, 1:25am

Thanks for posting your question @ZENMarkD. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

ZENMarkD · October 8, 2020, 6:02am

I have now included a demo pbix file that includes a table and a chart showing what I want. As mentioned before, the issue is that the cumulative forecast doesn’t equal the total monthly forecast, as shown in the table:

This is my issue that I can’t resolve. Again for your reference, here is the formula I am using to calculate the daily forecast:

Daily Forecast Allocation =

VAR MonthDays = CALCULATE( COUNTROWS( Dates ), ALL( Dates ), VALUES( Dates[Month & Year] ) )

VAR DailyFCST = CALCULATE( [Total Forecast], ALL( Dates ), VALUES( Dates[Month & Year] ) )

VAR DailyWeight = CALCULATE(AVERAGE(‘Daily Weights’[Weight %] ),

TREATAS( VALUES(Dates[DayOfWeek] ), ‘Daily Weights’[Day of Week] ))

return

IF( ISFILTERED( Dates[Date] ),

(DailyFCST * DailyWeight ) * 7 * DIVIDE( 1, MonthDays, 0 ),

[Total Forecast] )

Here is a screenshot of the data model in the demo:

The one in my actual file is a lot more involved but fundamentally is the same basic structure. The only major difference is that forecasts are at an item/location level.

Hopefully that’s enough info. Once again, any help would be immensely appreciated.

Practice Dataset.pbix (360.6 KB)

Greg · October 8, 2020, 1:57pm

Hi @ZENMarkD. Your [Cumulative FCST Allocation] formula is correctly adding the component values of your [Daily Forecast Allocation] measure. (I confirmed by exporting the raw data to excel and adding-it-up there.) Looks like the issue is with your daily forecast allocation forumla, which, while reporting a matching total value, does not have component values that add-up to your [Total Forecast] …

I’ll keep looking and if I find the issue I’ll let you know … hopefully more soon …
Greg

ZENMarkD · October 8, 2020, 9:38pm

Thanks Greg, I appreciate it. That is indeed the fundamental problem. Whether it’s to with different levels of granularity or not, I’m not sure either.

Look forward to hearing from you soon.

EnterpriseDNA · October 14, 2020, 1:07am

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

ZENMarkD · October 19, 2020, 5:01am

Hi All,

An update as to where I’m at. Still no answer, however it does seem the issue is with the Forecast Allocation measure.
What I did find is the formula works perfectly for February:

Obviously with an even number of weeks. So I would guess then I would need either a SWITCH or IF statement to alter the daily amount depending on the number of days in the month?
Then the $64,000 question becomes what would that calculation look like?

Hopefully that bit of info will trigger a solution, of which I would be very grateful.

Thanks again in advance.

Mark D

EnterpriseDNA · October 27, 2020, 2:56am

Hi @ZENMarkD, you may also want to check this video where Sam talked about Allocating Detailed Monthly Budgets Or Forecasts In Power BI Using DAX. You might get additional tips here.

EnterpriseDNA · October 29, 2020, 2:36am

Thanks to the contributors to this post. Due to the length by which this post has been active, we are tagging it as Solved. For more visibility please start a new topic within the forum and linked this thread for reference. You may check this how-to guide for reference - How To Use The Enterprise DNA Support Forum https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951