Cumulative total of Daily Average Sales

DAX DAX Calculations
1

Hi PBI brains!
Here’s my issue:

  1. Calculate the daily average sales for each day of the week. Exclude the current day (since more sales will come in during the day).
  2. Calculate cumulative total of daily averages and plot that to the end of the month.

I’ve managed to get the daily average (minus current day), but when I try make a cumulative column of the daily averages it fails and I’m sheesh, quite lost as to why its not working. :exploding_head:

This is an example of the data and where I’m trying to get to (pbix attached):

Date Day Average Daily Sales (less current day) Cumulative Avg Sales total
1/11/2020 Sun 111,008 111,008
2/11/2020 Mon 191,379 302,387
3/11/2020 Tue 130,055 432,442
4/11/2020 Wed 222,774 655,216
5/11/2020 Thu 183,473 838,689
6/11/2020 Fri 165,668 1,004,357
7/11/2020 Sat 114,360 1,118,717
8/11/2020 Sun 111,008 1,229,725
9/11/2020 Sun 191,379 1,421,104
10/11/2020 Mon 130,055 1,551,159
11/11/2020 Tue 222,774 1,773,933
12/11/2020 Wed 183,473 1,957,406
13/11/2020 Thu 165,668 2,123,074
14/11/2020 Fri 114,360 2,237,434
15/11/2020 Sat 111,008 2,348,442
16/11/2020 Sun 191,379 2,539,821
17/11/2020 Sun 130,055 2,669,876
18/11/2020 Mon 191,379 2,861,255
19/11/2020 Tue 130,055 2,991,310
20/11/2020 Wed 222,774 3,214,084
21/11/2020 Thu 183,473 3,397,557
22/11/2020 Fri 165,668 3,563,225
23/11/2020 Sat 114,360 3,677,585
24/11/2020 Sun 111,008 3,788,593
25/11/2020 Mon 191,379 3,979,972
26/11/2020 Tue 130,055 4,110,027
27/11/2020 Wed 222,774 4,332,801
28/11/2020 Thu 183,473 4,516,274
29/11/2020 Fri 165,668 4,681,942
30/11/2020 Sat 114,360 4,796,302

Any assistance much appreciated!
Claire
AvgSalesForecast.pbix (56.7 KB)

2

Hi @Claire, a similar topic was covered in this forum thread link. You might get additional tips here:

4

Hi @Claire, 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. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

5

Hi @EnterpriseDNA
Thanks for that, it was helpful to get the daily average, but am trying to use the daily average as a forecast.

So say its mid November, I can calculate the daily average up until the current date (excluding today) from that video. But I need that daily average to repeat itself in the table until the END of November (ie for dates that haven’t happened yet), then get the total sales up until 30th Nov. OR get a running total of the daily average up to mid November and use a simple linear regression plot to forecast to the end of the month.

I managed to get the total for each week day number to appear against the day number by doing this hack…

Daily Average =

IF([WeekDayNumx1] = 1, [AvgSalesSun_LessToday],

IF([WeekDayNumx1] = 2, [AvgSalesMon_LessToday],

IF([WeekDayNumx1] = 3, [AvgSalesTue_LessToday],

IF([WeekDayNumx1] = 4, [AvgSalesWed_LessToday],

IF([WeekDayNumx1] = 5, [AvgSalesThu_LessToday],

IF([WeekDayNumx1] = 6, [AvgSalesFri_LessToday],

IF([WeekDayNumx1] = 7, [AvgSalesSat_LessToday],

0)))))))

Then got the cumulative total by multiplying the average by the number of days

Sum(DailyAverages x NumDays) =

VAR Mon = ([AvgSalesMon_LessToday] * [CountRowsMon])

VAR Tue = ([AvgSalesTue_LessToday] * [CountRowsTue])

VAR Wed = ([AvgSalesWed_LessToday] * [CountRowsWed])

VAR Thu = ([AvgSalesThu_LessToday] * [CountRowsThu])

VAR Fri = ([AvgSalesFri_LessToday] * [CountRowsFri])

VAR Sat = ([AvgSalesSat_LessToday] * [CountRowsSat])

VAR Sun = ([AvgSalesSun_LessToday] * [CountRowsSun])

RETURN

Mon + Tue + Wed + Thu + Fri + Sat + Sun

So if you have a video somewhere on how to get a cumulative total of the daily average that would be awesome…I’ve hunted around on the interwebs and can’t find anything. I’ve got the cumulative total of daily sales fine, but when it comes to the averages it fails.
regards
Claire