Showing actuals and forecasts in the same chart with Power BI
HTML-код
- Опубликовано: 3 окт 2024
- 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!!!
It really makes more clear to me on when to use removefilters and keepfilter. Thanks for the great videos!🙏
This is the only video on RUclips that is worth watching when it comes to Rolling Forecast! Thank you very much
Another great example on how to use remofilters and keepfilters. thank you for sharing
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
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!
Brilliant... I've only watched a couple of videos but there are already making my life a lot easier... Thank you
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.. 🤟👍
Got my formula to work but have to manually update the last date each month, but this definitely helped.
Thank you. I have been learning from your book and more for 4 months. Very helpful. Merry Christmas.
Thanks - Happy Holidays!
Neat way to use remove filter and keep filter functions. Thank you 👍
Amazing ! I learn too much with the DAX formulas shown.
Very useful, Alberto. Thanks very much!
Awesome! Thanks a lot for the guidance!
Thank you. I need that this afternoon!
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.
Thanks for share a great content with us.
Nice and super explaining.
Great stuff again Alberto. Many thanks
This guy is a BI master
merry christmas, SQLBI guys! take good rest, relax :)
from Brazil. Great video.
Great stuff as per usual. Best Christmas present :).
You are amazing, thanks for sharing
Thanks a ton ! Alberto !
What do you do if you do not have the forecast, how do you calculate the measure?
Awesome. Thanks Alberto!
Very helpful thank you Alberto!
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 ?
Alberto... one person has given you a thumbs-down. They must have had a bad day, I guess :) Thanks for the video.
how can ich calculate the forecast total sum for the last Actual Sales Period?
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
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
great content.... Thanks a lot !
Conteúdo sempre da melhor qualidade. Obrigado por partilhar
Extremely useful!
Beautiful, thank you.
Thank you. Just wondering how to do this yesterday, after trying to add a What If measure to create a forecast amount.
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?
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/
Thanks for the christmas gift !
Hi, how have you calculated forecast column?
that should be the whole point of the video
I am also interested
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.
Hello, thank you for the video, how have you calculated forecast measure?
Thank you so much
Excellent!!!
what is the DAX for forecasting?
You are god of DAX 🙏🏿
Nice trick!
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?
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/
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
Can you please share how you calculated the Forecast? Thank you!
Please do a video on how to calculate top Quartile and bottom Quartile performance
Hi Alberto
Mary christmas
and very very happy return of the day
wish you a very happy birthday.
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
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.
WONDERFUL !!!
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?
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
Ola Senyor, where you are getting forecast from ? From a planning tool or there is a way to do it in power bi ?
Can you please explain [Forecast] measure, without this how can I write RF measure.
please explain it.
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.
In this use case, goal is to go from 2:10 to 2:25 visualization.
Can we add a legend as well to this graph?
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.
Great!
Hello, How do I make the forecast column ?
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.
Great ....
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.
What is the Dax for forecast in the var remainingforecast
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
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.
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)
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.
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!
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).
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