How to calculate Future Sales

[Mike921]

Hi all, i hope someone can help me to understand to do same in Power Bi, actually in my Bi model use the follow table:
Calendar,

actual sales.

I calculate the variation between period and geometric average.

i try different way but without results, i hope someone can help me to resolve my problem.

[Wilson_ · Accepted solution]

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

[Mike921]

THANKS FOR EVERYTHING and thanks for the course i start to study asap 🙂

[Wilson_]

Hi Mike,

It sounds like what you might be looking for is:

1. To checks if there are actuals for the week of the year
2. If a future period, multiply by a constant value (ie: the average variation) for every additional week in the future.

If so, the three measures (Sales Amount, Average Variation and Forecast) below might work:

Sales Amount = SUM ( 'Actual Sales'[Amount] )
Average Variation = ???

Forecast =
VAR AvgVariation = [Average Variation]
VAR LastActualSalesDate = LASTNONBLANKVALUE ( Calendar[Date], [Sales Amount] )
VAR LastActualSales = CALCULATE ( [Sales Amount], LastActualSalesDate )
VAR NumWeeksAfterLastActualSalesDate = DATEDIFF ( LastActualSalesDate, MAX ( Calendar[Date] ), WEEK )

RETURN
COALESCE ( [Sales Amount], LastActualSales * (1 + [Average Variation] ) ^ NumWeeksAfterLastActualSalesDate

Note: Fill in your own average variation calculation for the Average Variation measure; I'm not entirely following the variation column or the average variation of 20.19% you're showing. If this doesn't work, I may not be picturing your data in my head correctly. It might help if you created a mock sample pbix file and linked to it here so I can play with it and send it back to you. (If you're not sure how to do that, please check out the pinned thread in this forum.)

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

[Mike921]

Hi Wilson, how are you? thanks for the help i try to adapt your dax to my report but maybe i do something wrong. Follow the link with my pbix, last thing i need calculate the future sales from last data preorder to release week for the product i filter. Documenti

[Wilson_ · Accepted solution]

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

[Mike921]

Hi Wilson, thx a lot i try to apply your solution to my original pbix and came back to you with a feedback. in the future if i need any advice can text you directly? 

[Wilson_]

Mike,

I forgot to mention that one of the other things I changed in your sample file is your date table. It currently only goes through 2024/1/31. Expand that out to the rest of the year. Otherwise, there are no future weeks in your data model to forecast to. That should allow you to forecast out further.

To your other question of whether you can message me directly for help in the future, thank you for asking. However, the answer is no. If I'm helping somebody for free, I want it to be publicly so that other members of the community can also benefit from the discussion if they run into the same issue in the future. Thank you for understanding! 🙂

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

[Mike921]

Thx a lot for the help you are a master of Bi, i expanded the calendar to reach end of year 2024, but forecast the data from the last preorder to max calendar date, i need to stop the forecast when the date reach the relase date. how can i do? update. i edit the sample i think i'm near the solution but something not work properly. sample.pbix

[Wilson_]

Mike,

Try this updated forecast measure:

Forecast = 
VAR MaxDate = CALCULATE ( MAX ( 'Date'[Date] ), REMOVEFILTERS ( Preorder ) )
VAR MaxWeekYear = CALCULATE ( MAX ( 'Date'[YearWeek] ), REMOVEFILTERS ( Preorder ) )
VAR ReleaseWeek = INT ( [release week] )
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)
VAR Display = IF ( MaxDate >= FirstActualSalesDate && MaxWeekYear <= ReleaseWeek, Result )

RETURN Display

Added another variable to calculate the selected WeekYear and a variable to calculate the release week. Note: I also had to convert release week to an integer because Catalog[Release Week] is a text field. For multiple reasons, this should probably be an integer field.

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

[Mike921]

Hi wilson, i apply your dax but get stuck, forecast 2 it's your old dax, forecast is the new one.
Sorry Wilson, please don't hate me.... follow the pbix. sample.pbix

[Wilson_ · Accepted solution]

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