Forecast with 8wk day avarage

[Anonymous]

Hi all,

Very new to this forum. Only started working with power-bi a couple of weeks ago.

Until now I worked almost 15 years with all the (IBM) Cognos bi solutions. But sometimes you have to take a leap and look at other options for your customers. And Let's say, I really like the more visual option I can do with PB.

But that said, still a lot to learn and adjust to a completely new workflow of working. (Always SQL minded, sow still adjusting to dax).

I could use some help with a problem where I cannot seem to find the correct solution.

Business case:

- Time Dim ->1.n ->Fact table

- Sales amount (on day level)

- Product group

Cumulative Sales and forecast for the current year on some min / max scenario.

I already had forecasting working, where I looked at the sales on a date and Sameperiodelastyear and made a working forecast on average day sales from last X years.

Example: Actual cummalative sales is the solid line and the dotted is the cummalative forecast from that point.

So far so good:

Problem:

Now the business wants the same visual only, the forecast needs to be on the Latest average from the last 8wk of data.

No problem, and made measure a rolling average on day for 56 days (8 weeks).

Sow now i have. (history until now)

Dates(until now) //Sales ammount // day average // 

Last day of sales =  10 Jan.

Last day average on the 10e jan = 50

Now I want a measure that Dates between max(sales,date)+1 and endofyear (12/31/2022) = 50
But what I try i get a measure that only gives the 50 over the total 1 jan until 31 dec...

Example of the end result:

Solid line is the cumulative sales , the dotted line is the linear forecast on the cumulative average sales (50)

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You can try the following measures. I attached a sample pbix at bottom. 

Last day of sales = MAXX(ALL(Sales),Sales[Date])

Last 8wk day average =
VAR __lastSalesDate = [Last day of sales]
RETURN
DIVIDE(CALCULATE(SUM(Sales[Sales]),ALL('Date'[Date]),DATESINPERIOD('Date'[Date],__lastSalesDate,-56,DAY)),56)

Cumulative Sales = CALCULATE(SUM(Sales[Sales]),FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date])))

Cumulative Forecast = SUMX(FILTER(ALL('Date'),'Date'[Date]>[Last day of sales]&&'Date'[Date]<=MAX('Date'[Date])&&'Date'[Date]<=EOMONTH([Last day of sales],12-MONTH([Last day of sales]))),[Last 8wk day average])

Cumulative Total = [Cumulative Sales] + [Cumulative Forecast]

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.