Adding a daily average cumulatively but not on weekends

[BUK2020]

Hi,

I am having some trouble, I am trying to forward forcast cumulatively based on the average of the past 14 days sales. This is what my table looks like:

Date            14 Day average         14 Day average minus weekends         Cumulative Average      

7/5/20              19                                            19                                                19

8/5/20              19                                            19                                                38

9/5/20              19                                            

10/5/20            19                                            

11/5/20            19                                            19                                                95

As you can see, I have managed to get the 14 day average to not show on weekends using IF([Day of week] >= 6, blank(), show value). However, when I add them cumulatively (last column), the calculation seems to be including the missing days (saturday and sunday), how do i avoid this?

Here is my cumulative calculation:

14 Day Cumulative Daily Run Rate =

VAR AvgRate = [14 Day Average Daily Run Rate]

RETURN

CALCULATE(

SUMX( SUMMARIZE('Date Table','Date Table'[Date], "RunRate", AvgRate),[RunRate]),

FILTER(ALLSELECTED('Prediction Line Calendar'),'Prediction Line Calendar'[Date] <= MAX('Prediction Line Calendar'[Date])))

How do i fix this?

[amitchandak · Accepted solution]

@BUK2020 , see if this can work

14 Day Cumulative Daily Run Rate =
VAR AvgRate = [14 Day Average Daily Run Rate]

RETURN
CALCULATE(
SUMX( SUMMARIZE('Date Table','Date Table'[Date], "RunRate", AvgRate),[RunRate]),
FILTER(ALLSELECTED('Prediction Line Calendar'),'Prediction Line Calendar'[Date] <= MAX('Prediction Line Calendar'[Date]) && WEEKDAY([Date],2) <6))

[amitchandak · Accepted solution]

@BUK2020 

Create a working day in date calendar.

like

Work Day = if(WEEKDAY([Date],2)>6,0,if(Format([Date],"MMDD")="1225",0,1))

Something like this and then use a filter [Work Day] =1

[BUK2020]

To add more context, I want the table to look like this... ( cumulative total stays the same on 9th and 10th as they are weekends).

Date            14 Day average         14 Day average minus weekends         Cumulative Average      

7/5/20              19                                            19                                                19

8/5/20              19                                            19                                                38

9/5/20              19                                                                                                38

10/5/20            19                                                                                                38

11/5/20            19                                            19                                                57

[amitchandak · Accepted solution]

@BUK2020 , see if this can work

14 Day Cumulative Daily Run Rate =
VAR AvgRate = [14 Day Average Daily Run Rate]

RETURN
CALCULATE(
SUMX( SUMMARIZE('Date Table','Date Table'[Date], "RunRate", AvgRate),[RunRate]),
FILTER(ALLSELECTED('Prediction Line Calendar'),'Prediction Line Calendar'[Date] <= MAX('Prediction Line Calendar'[Date]) && WEEKDAY([Date],2) <6))

[BUK2020]

@amitchandak Thank you so much, it worked.

However, my next problem is excluding public holidays as well as weekends, is there any way to do this? For example tomorrow is a public holiday in the UK, i would like my cumulative total tomorrow to be the same as today as there will be no sales tomorrow.

[amitchandak · Accepted solution]

@BUK2020 

Create a working day in date calendar.

like

Work Day = if(WEEKDAY([Date],2)>6,0,if(Format([Date],"MMDD")="1225",0,1))

Something like this and then use a filter [Work Day] =1

[BUK2020 · Accepted solution]

@amitchandak 

Thank you so much, that worked perfectly. Just for clarification i did some more searching and found this post https://community.powerbi.com/t5/Desktop/DATEDIFF-Working-Days/td-p/130662 I added the formulas on this page to my "Prediction Line Date Table" and it works perfectly.

Thank you very much for your help! Have a great day!