How to calculate Future Sales

Hi Mike,

Ah, I see you're also filtering by product and your fact table doesn't have the date, only the YearWeek.

In that case, the below should work for your Forecast measure instead. (Mainly I've added a variable to capture the max date of the row in the visual.)

Forecast = 
VAR MaxDate = CALCULATE ( MAX ( 'Date'[Date] ), REMOVEFILTERS ( Preorder ) )
VAR AvgVariation = [Avg Variation]
VAR FirstActualSalesDate = CALCULATE ( FIRSTNONBLANK ( 'Date'[Date], [preorder] ), REMOVEFILTERS ( 'Date' ) )
VAR LastActualSalesDate = CALCULATE ( LASTNONBLANK ( 'Date'[Date], [preorder] ), REMOVEFILTERS ( 'Date' ) )
VAR LastActualSales = CALCULATE ( [preorder], REMOVEFILTERS ( 'Date' ), 'Date'[Date] = LastActualSalesDate )
VAR NumWeeksAfterLastActualSalesDate = DATEDIFF ( LastActualSalesDate, MaxDate, WEEK )
VAR Result = COALESCE ( [preorder], LastActualSales * (1 + [Avg Variation] ) ^ NumWeeksAfterLastActualSalesDate)

RETURN 
IF ( MaxDate >= FirstActualSalesDate, Result )

Something to consider in the future:

While the above measure works for your data model, I strongly encourage you to learn more about data modeling, so you can avoid many-to-many relationships in the future (and also write measures more easily!). For example, in your scenario where the preorders are captured at a weekly level, instead of capturing the WeekYear in the Preorder table, I would suggest having a WeekStart column instead for example. That way, you can have a one-to-many relationship instead.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

Mike,

Apologies, I made two subtle but important changes early on that I forgot to mention.

• The Catalog to Preorder relationship should not be bi-directional. In general, relationships should only filter from dimensions (ie: Catalog) to facts (ie: Preorder).
• Replace Preorder[Product] with Catalog[Product] in your product slicer. Again, in general, you should default to using columns from dimension tables in your slicers.

When you do, you should see the below:

This free course on data modeling from SQLBI should be tremendously helpful for you if you will be working with Power BI and DAX (and data analysis in general!) often. I hope it is as beneficial for you as it was for me! 🙂

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)