cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
maartendd
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.

 

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.Naamloos.png

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)
test2.png

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @maartendd 

 

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]

22012602.jpg

 

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

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

Hi @maartendd 

 

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]

22012602.jpg

 

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

lbendlin
Super User
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.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors