cancel
Showing results for
Did you mean:
Regular Visitor

## Forecast with 8wk day avarage

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.

- 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)

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Super User

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.

Announcements