Forum Discussion
How to calculate Future Sales
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.
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?)
10 Replies
Wilson_Memorable Member
Hi Mike,
It sounds like what you might be looking for is:
- To checks if there are actuals for the week of the year
- 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] ) ^ NumWeeksAfterLastActualSalesDateNote: 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?)- Wilson_
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?)
THANKS FOR EVERYTHING and thanks for the course i start to study asap 🙂