Showing actuals and forecasts in the same chart with Power BI

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?aff=yt
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin...

[@RACHIMSAJA]

It really makes more clear to me on when to use removefilters and keepfilter. Thanks for the great videos!🙏

[@aatsw]

These short videos are extremely helpful and time-efficient, much better than long videos. Please keep them coming. Thanks!

[@thepitalstore1383]

Plz....we need ur guide @Alberto_Ferrari

[@CaCalegarii]

I am not used posting comments in videos, but you are simply the best! What an easy and straight forward explanation. Thank you!!!

[@sarris2412]

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

[@andresramirezortega6218]

This is the only video on KZbin that is worth watching when it comes to Rolling Forecast! Thank you very much

[@mehdihammadi6145]

Another great example on how to use remofilters and keepfilters. thank you for sharing

[@evedickson2496]

Brilliant... I've only watched a couple of videos but there are already making my life a lot easier... Thank you

[@NaraMeerammaTrust]

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.

[@SQLBI]

Our pleasure!

[@siddheshamrutkar8684]

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.. 🤟👍

[@harryhai3116]

Thank you. I have been learning from your book and more for 4 months. Very helpful. Merry Christmas.

[@SQLBI]

Thanks - Happy Holidays!

[@Bharath_PBI]

Neat way to use remove filter and keep filter functions. Thank you 👍

[@suki9860]

Very useful, Alberto. Thanks very much!

[@GentilOliveira]

Thanks for share a great content with us.

[@judeLondon]

Great stuff again Alberto. Many thanks

[@scotttokaryk3714]

Thank you. I need that this afternoon!

[@gabrielmorais7312]

Awesome. Thanks Alberto!

[@Granty707]

Brilliant video. Really helped me out at work 😀

[@afdarizki]

Awesome! Thanks a lot for the guidance!

[@mehdifadhli6413]

Very helpful thank you Alberto!

[@dchapman74055]

Got my formula to work but have to manually update the last date each month, but this definitely helped.

[@felipesignorellireis7839]

from Brazil. Great video.

[@aryansinanan9878]

Great stuff as per usual. Best Christmas present :).

[@borismario]

Amazing ! I learn too much with the DAX formulas shown.

[@RakeshSaha1705]

Nice and super explaining.

[@NoShadowOfDoubt1]

Beautiful, thank you.

[@suziearnold7585]

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 ?

[@gilbertosegoviano7701]

You are amazing, thanks for sharing

[@VladMZ]

Extremely useful!

[@gabriellegall8278]

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

[@vilmarsantos3419]

Conteúdo sempre da melhor qualidade. Obrigado por partilhar

[@ymohamed3154]

Great video. Can you please make us a video of how you calculated the forecast. Thanks

[@anuragbawankar685]

great content.... Thanks a lot !

[@oricchannel2811]

Thanks for the christmas gift !

[@Milhouse77BS]

Thank you. Just wondering how to do this yesterday, after trying to add a What If measure to create a forecast amount.

[@ekanayake1]

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.

[@cucotorres]

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.

[@BrunoSilva-nj7gy]

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.

[@travelkare]

Thanks a ton ! Alberto !

[@PSModelling]

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.

[@aashisehrawat]

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?

[@pietman1000]

This guy is a BI master

[@wmfexcel]

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?

[@SQLBI]

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.

[@Datanometrics]

Noticed this, thanks for sharing

[@mshparber]

Excellent!!!

[@CarlosEspinoza-me9oi]

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.

[@shatakshiagrawal3062]

Thank you so much

[@zeisslaimen1]

merry christmas, SQLBI guys! take good rest, relax :)

[@asn85uk]

Ola Senyor, where you are getting forecast from ? From a planning tool or there is a way to do it in power bi ?

[@yashjoshi9083]

WONDERFUL !!!

[@gargierawatt]

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

[@pthapa55]

Can you please share how you calculated the Forecast? Thank you!

[@ck2610]

how can ich calculate the forecast total sum for the last Actual Sales Period?

[@marcnichols164]

What do you do if you do not have the forecast, how do you calculate the measure?

[@wexwexexort]

Nice trick!

[@riazuddin7493]

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.

[@giri41]

Please do a video on how to calculate top Quartile and bottom Quartile performance

[@oconnorra]

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?

[@sbn4862]

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?

[@totvabe1]

Great!

[@rosev.7778]

What is the Dax for forecast in the var remainingforecast

[@anuragbawankar685]

Why am I getting values in actual sales after the max date i.e. Today date ?????? (in my project) what could be the reason?

[@lionels839]

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?

[@SQLBI]

You can use ALL instead of REMOVEFILTERS. The samples are available also for Excel and they use that.

[@BbabbittGolf]

Great video! I would love to see the measure used to create the forecast!

[@SQLBI]

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!

[@BbabbittGolf]

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

[@vedikatrivedi2019]

Hello, How do I make the forecast column ?

[@narendrakumar-im7en]

Can we add a legend as well to this graph?

[@shrikantthakur7127]

Hi How to create Forecast DAX can you share

[@md.mohiuddinchowdhury8021]

what is the DAX for forecasting?

[@3danim8r1]

Great ....

[@vineetyoutubr5]

You are god of DAX 🙏🏿

[@RTSWatson]

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.

[@SQLBI]

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/

[@jameszhou162]

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.

[@SQLBI]

You can do a dynamic allocation as explained here: www.daxpatterns.com/budget/

[@userasdf1546]

I wish you would tell us what hotkeys you are pressing at 7:00. I can't get my forecast column to show up :(

[@userasdf1546]

Nevermind. I had to create a measure and call the measure instead of the column.

[@user-ed6wl2bm5g]

Can you please explain [Forecast] measure, without this how can I write RF measure. please explain it.

[@sekousoro6309]

Great

[@omardaraz1027]

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

[@omardaraz1027]

any update!!!

[@ashishmohan4707]

Hi Alberto Mary christmas and very very happy return of the day wish you a very happy birthday.

[@clovissantiago2195]

How can i get the original measure forecast to calculate and combine the values after? Someone help me? Thanks.

[@nicholaskeogh1121]

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.

[@jurgenguns1925]

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...

[@SQLBI]

It should work the same for the future months...

[@jurgenguns1925]

@@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.

[@jurgenguns1925]

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.

[@SQLBI]

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?

[@jurgenguns1925]

@@SQLBI because I'll receive daily new data report with new sales figures and they are collected by period. (Example period 202101)

[@olemew]

In this use case, goal is to go from 2:10 to 2:25 visualization.

[@dariuszspiewak5624]

Alberto... one person has given you a thumbs-down. They must have had a bad day, I guess :) Thanks for the video.

[@roxanarodriguez1828]

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

[@SQLBI]

Compare your file with the file you can download in the related article (see video description).

[@semihfndkc6993]

Hi, how have you calculated forecast column?

[@victorsoto9075]

that should be the whole point of the video

[@ymohamed3154]

I am also interested

[@droctowiz5968]

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 ?

[@amarnadhgunakala2901]

New content

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!

[@SHAUNVINDA]

super mario