Showing actuals and forecasts in the same chart with Power BI
Вставка
- Опубліковано 5 лют 2025
- In budgeting reports, a common requirement is to show future sales forecast and actual sales volume on the same line chart. Learn how to achieve this goal using DAX.
Article and download: sql.bi/73386?a...
How to learn DAX: www.sqlbi.com/...
The definitive guide to DAX: www.sqlbi.com/...
These short videos are extremely helpful and time-efficient, much better than long videos. Please keep them coming. Thanks!
Plz....we need ur guide @Alberto_Ferrari
I am not used posting comments in videos, but you are simply the best!
What an easy and straight forward explanation.
Thank you!!!
This is the only video on UA-cam that is worth watching when it comes to Rolling Forecast! Thank you very much
Alberto, you are the best! No matter how much time it takes but I am confident that this channel will reach million subscribers without a doubt...Best wishes
It really makes more clear to me on when to use removefilters and keepfilter. Thanks for the great videos!🙏
Alberto, you are doing extraordinary job. No words to express our happiness. helping us a lot in our day to day work on Power BI. Once again thanks a lot. I am a big fan of you and your videos.
Our pleasure!
Another great example on how to use remofilters and keepfilters. thank you for sharing
It's truly an amazing video.. Thinking in the similar and same direction but get stucked sometimes but you look very simple don't know how but definitely it requires sheer concentration, dedication, hard work and more importantly understanding of the requirement from business perspective.. Superb.. 🤟👍
Brilliant... I've only watched a couple of videos but there are already making my life a lot easier... Thank you
Thank you. I have been learning from your book and more for 4 months. Very helpful. Merry Christmas.
Thanks - Happy Holidays!
Got my formula to work but have to manually update the last date each month, but this definitely helped.
This guy is a BI master
Neat way to use remove filter and keep filter functions. Thank you 👍
Awesome! Thanks a lot for the guidance!
Very useful, Alberto. Thanks very much!
Great video. Can you please make us a video of how you calculated the forecast. Thanks
Brilliant video. Really helped me out at work 😀
This may not be exactly what you're looking for but the COALESCE function can merge two measures overriding any blanks with values from the other measure.
Amazing ! I learn too much with the DAX formulas shown.
Can you please share how you calculated the Forecast? Thank you!
Great stuff again Alberto. Many thanks
Thank you. I need that this afternoon!
What do you do if you do not have the forecast, how do you calculate the measure?
from Brazil. Great video.
Nice and super explaining.
Thanks for share a great content with us.
Great video! Thank you for sharing your knowledge! I was wondering if it is possible to link it to a month/year slicer. The behaviour I need is; if I filter for Jun/2023 I would see actual sales from Jan to Jun 23 and Forecast from Jul to Dec 23. Is it possible, regardless the current date? Thank you in advance.
This is a great video. A scenario I have been trying to solve is when we reforecast each month but do not want to over ride prior months data so we can tell- are we getting better at forecasting ?
You are amazing, thanks for sharing
Great video! I would love to see the measure used to create the forecast!
Download the sample file following the instructions in the description. However, the forecast here is just fictitious, it's not the value of the article!
Thank you!
Also, how would your remaining forecast calculation change if the sales table had two dates to work off of - sales date & order date for example?
@@SQLBI
how can ich calculate the forecast total sum for the last Actual Sales Period?
Thanks a ton ! Alberto !
You are god of DAX 🙏🏿
Alberto... one person has given you a thumbs-down. They must have had a bad day, I guess :) Thanks for the video.
Hello, thank you for the video, how have you calculated forecast measure?
Ola Senyor, where you are getting forecast from ? From a planning tool or there is a way to do it in power bi ?
Awesome. Thanks Alberto!
Great video however can someone help me out explain how did the remaining forecast number for Aug came up to what it shows on the screen Many thanks
what is the DAX for forecasting?
Thank you. Just wondering how to do this yesterday, after trying to add a What If measure to create a forecast amount.
Thanks for the step by step demonstration. It helps a lot in understanding the filter context! :)
Btw, shall the formula be 'Date[Date] '> LastDateWithSales so that last date of actual sales will not be included?
You're right, thanks for catching it!
Indeed, it was already correct in the article and in the demo file you can download, it was a typo in the recording.
Noticed this, thanks for sharing
Amazing, can we also do this if I need to add legends in the line chart? For instance, showing revenue for 3 business units and highlighting forecasts by dotted lines?
In this use case, goal is to go from 2:10 to 2:25 visualization.
great content.... Thanks a lot !
Very helpful thank you Alberto!
Extremely useful!
Thanks for the video ! Very insightful
What if we want to have 2 measures, e.g. if we want to show the forecast in dotted line and the sales in a solid line ?
Should I just split the combined measure into two measures using IF ? or if there a better solution
What is the Dax for forecast in the var remainingforecast
Great stuff as per usual. Best Christmas present :).
Thank you - I don't understand how the budget table knows to split by the relevant month as it does not have a month field.
The Forecast measure performs a dynamic allocation by month based on sales of the previous year. That part is not described neither in the video nor in the article. You can find the formula in the sample file you can download from the article page. A more complete description of the allocation technique is available here: www.daxpatterns.com/budget/
Hi Alberto. Great video. In the same example what would be the best way to have a note to say which are Actual Figures and which are forecast. Probably as a heading against the month. Thank you in advance.
Can we add a legend as well to this graph?
why was there a $8,338.05 difference between RF and Remaining Forecast?
Hi, how have you calculated forecast column?
that should be the whole point of the video
I am also interested
Thanks for the christmas gift !
merry christmas, SQLBI guys! take good rest, relax :)
Very best method, i tried did such chardboard with IF functions, Thank you!!!
LastDateWithSales is varable and if even I use All modificator in varable it owerwrites all filter contexts whatever?
I have a column in my table name reason for leaving and based on that I want to add another column which is Volunteer attrition or Involunteer attrition i.e. if reason for leaving for an employee appears as Retired then the new column should show Involunteer attrition whereas for resignation it should show as volunteer attrition. Can you help me add this column please? Also how can I calculate the percentage of volunteer and involunteer attrition based on the total attrition for the whole year which is 1157.
I wish you would tell us what hotkeys you are pressing at 7:00. I can't get my forecast column to show up :(
Nevermind. I had to create a measure and call the measure instead of the column.
Excellent!!!
Why am I getting values in actual sales after the max date i.e. Today date ?????? (in my project) what could be the reason?
Hi How to create Forecast DAX can you share
hi Alberto, i think it would be not good to ask but still want to know how u determind or create forecast column. i have sale data so how can i say this would be my forecast. please help
any update!!!
I have the same question
Nice trick!
Thank you so much for explaining so clearly! I have a similar but different scenario with targets and actuals, but no date! Just an index. if i attempt this, i get teh first part right, but not the second:
Remaining Expected Steps =
VAR LastLegalStep =
CALCULATE(
MAX( FactFiles[LegalSubStepIndex] ),
FILTER( DimCurrentStep, DimCurrentStep[Legal Step] = "Current"))
VAR RemainingLegalSteps =
CALCULATE(
[Expected Days],
KEEPFILTERS(FactFiles[LegalSubStepIndex] >= LastLegalStep)
)
RETURN
RemainingLegalSteps
Can you point me in the right direction?
Beautiful, thank you.
Hello, How do I make the forecast column ?
Thank you so much
Hello Alberto. I love what you do. One dumb question please: my power pivot version does not include REVOMEFILTERS. How can I replace it in your book DAX Patterns?
You can use ALL instead of REMOVEFILTERS. The samples are available also for Excel and they use that.
Can you please explain [Forecast] measure, without this how can I write RF measure.
please explain it.
Please do a video on how to calculate top Quartile and bottom Quartile performance
great example. but in majority of cases, forecast is done on Monthly level rather on daily. so i guess it would be only possible to use day extrapolation to combine remaining sale forecast to actual.
You can do a dynamic allocation as explained here:
www.daxpatterns.com/budget/
Amazing! Could it works for a daily forecast? I need it for a current month daily, but some different. Example the 1st day (mon, tue, wed...etc) of current month versus the 1st day (mon, tue, wed...ect) from the month last year. Ex: Thu 01, Jul 2021 vs Thu 02, Jul 2020; Fri 02 Jul 2021 vs Fri 03 Jul 2020 and so on.
Hi Alberto. Thanks for this video. But i have a doubt, which visual are you using? Because I'm using line chart and can't do that.
Conteúdo sempre da melhor qualidade. Obrigado por partilhar
WONDERFUL !!!
Hi Alberto, you can explain these trainings as the best!
But how to handle if the last date of Sales is always the month? Within my data of sales I have only month period...
It should work the same for the future months...
@@SQLBI Yes, for future months result is the same however, his calculation for last month sales will always be complete month and not day of date. So he includes the totale of running month in the forecast.
In practice it should measure the diff of the first forecast month and the actual sales of that month to have the remaining value of the running month.
But if the date is always at the end of the month, how is it possible to know what are the dates covered by the data available?
@@SQLBI because I'll receive daily new data report with new sales figures and they are collected by period. (Example period 202101)
Hi Alberto
Mary christmas
and very very happy return of the day
wish you a very happy birthday.
thank you computer nerd version of remus lupin
Great ....
How can i get the original measure forecast to calculate and combine the values after? Someone help me? Thanks.
did you ever figure this out? seems like its a pivotal measure for this to work but i can't find where he's created it.
Great!
Hi I dont know if someone can help me, I did exactly the same, I even download the source and duplicate every single measure but when I put total sales it puts the same amount but when I have filter CY 2010 and put the forecast measure it just show blank, when I remove the filter it shows values, why could that happen?¡
Compare your file with the file you can download in the related article (see video description).
Great
Hey its Xmas. Please do not post Videos in the silent time. Even that all are usually great! Spend time with your family and relax!
I understood the remove and keep filters, but why did the expression override the contextual filters in the first place ? Is it because of the removefilters in the expression of the variable ?
New content
super mario