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] ) )
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
sampleData.csv (1.7 KB)
DailyWeights.csv (104 Bytes)