Forecast with Average Sales MTD / Counting Working Days Rolling

[Anonymous]

Hey there!

Iam quite new to Power BI and iam trying to calculate the average Sales per working days (Rolling average) in order to generate a forecast.

I hope the table underneath will explain everything.

Im struggling with calculating the column "Working Days Rolling Count" and the column "Average Sales MTD/Forecast"

I have two tables. One with Sales Data and one Date Table that shows only working days.

Does anyone have some helpful hints how to solve this?

Date	Sales	Sales MTD	Working Days Rolling Count	Average Sales MTD / Forecast
01.08.2022	100	100	1	100
02.08.2022	150	250	2	125
03.08.2022	125	325	3	108,33
04.08.2022	130	455	4	113,75
05.08.2022	90	545	5	109
08.08.2022	110	655	6	109,16
09.08.2022	140	795	7	113,57
10.08.2022			8	113,57
11.08.2022			9	113,57
12.08.2022			10	113,57
15.08.2022			11	113,57
16.08.2022			12	113,57
17.08.2022			13	113,57
18.08.2022			14	113,57
19.08.2022			15	113,57
22.08.2022			16	113,57
23.08.2022			17	113,57
24.08.2022			18	113,57
25.08.2022			19	113,57
26.08.2022			20	113,57
29.08.2022			21	113,57
30.08.2022			22	113,57
31.08.2022			23	113,57
TOTAL	795	795	23	2595,93

[v-easonf-msft]

Hi， @Anonymous 

Please try calculated columns like:

Working Days Rolling Count1 = CALCULATE(COUNT('Table'[Date]),FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])))

Working Days Rolling Count2 = CALCULATE(COUNT('Table'[Date]),FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])&&'Table'[Sales]<>BLANK()))

Average Sales MTD / Forecast = 'Table'[Sales MTD]/'Table'[Working Days Rolling Count2]

If it doesn't work, please a sample file for further research。

Best Regards,
Community Support Team _ Eason

[Anonymous]

Hello @v-easonf-msft !
Thank you very much!!!

Based on this, is it possible to calculate column "Sales" with all the Actual sales data + the latest "Average Sales MTD / Forecast"?
Because that is actually the main goal im trying to achieve.

So for now the missing spaces in column "Sales" should be 113,57 and sum up to a total. That would be my rolling forecast, that changes everyday with further actual sales 😃

[v-easonf-msft]

Hi， @Anonymous 

Try to add a new calculated column to replace your original column 'Sales'.

New Sales = IF(ISBLANK('Table'[Sales]),'Table'[Average Sales MTD / Forecast],'Table'[Sales])

Best Regards,
Community Support Team _ Eason

[Anonymous]

Hi @v-easonf-msft ,

i used your ideas and had to adapt them to my file, which might be a little bit more complex, but so far everything worked out perfectly. But! Like you can see on the screenshot Power BI doesn´t sum up "New Sales" correctly...

My Measures are:

Revenue FY = SUM(Wertposten_Excel_LIVE[IST-Umsatz])

Revenue FY MTD_DailySumUp = TOTALMTD([Revenue FY], 'Date Table Working Days'[Datum])

Avg. Rolling Revenue FY MTD = DIVIDE([Revenue FY MTD_DailySumUp],[WorkingDays<=Today])

New Sales = IF([Revenue FY]=0,[Avg. Rolling Revenue FY MTD],[Revenue FY])

New Sales MTD = TOTALMTD([New Sales], 'Date Table Working Days'[Datum])

I couldnt create a calculated column within my sales data, so i had to create a measure with the same content.

Any ideas how i can fix this?

[lbendlin]

Yours looks more like a Total Cumulative than a Rolling average.  "Rolling" usually means a sliding window, like "the last 10 working days".

Please clarify.

[Anonymous]

Hello @lbendlin !
Thank you so much for your response!
Well yes i might used the wrong expression for what im trying to achieve, im sorry for that.

Like you can see in my response to @v-easonf-msft , my sliding window is actualy fixed on the right side and only sliding on the left inwards with days gone and new daily sales.