What's the missing LINK?! Combining ACTUALS and FORECAST as one LINE in Power BI

[@sukoi2113]

You covered so much DAX knowledge in a single short video. That's beauty of your channel. I never forget to mention your channel to any power bi enthusiast. A lot of Respect.

[@HowtoPowerBI]

I appreciate that! thank you Rajneesh! 😊

[@alexrosen8762]

You are just born for this. Your tutorials are really at a high level.

[@skylanakiasr35]

I've been searching for this video/topic for over the past 3 years. i'm crying from joy 😂 Thank you!!!

[@anamqureshi802]

Your way of teaching is so simple and up to the mark. :)

[@HowtoPowerBI]

Glad you think so!

[@gianniskoutsoubos8896]

Incredible video!Very elaborative and i deeply cherished the walkthrough on the DAX how-to's with different approaches! Keep up the great work!!

[@satindersahota2981]

Thank you so much for explaining the Dax in details. This video has helped in getting the exact same output. Excellent knowledge

[@RamzyTheDad]

Dude.... this just helped me to solve a problem. Thank you Bas!

[@esaegess]

Thank you so much for your videos. I've got a different setup and I'm not able to combine actuals and forecast. Actuals are on a daily bases and forecast is on a yearly bases. For my combined actuals and forecast, I've got to a) subtract the total of the actuals from the total forecast (one value for the year) and b) calculate the daily forecast for all days after the latest actual. Would be great if you could get in touch with me or if you could create another great video ;-). Thank you!

[@alottafrancium]

This is very helpful! Thank you! Concise explanations with different ways to look at the problem with what the results are.

[@markusbajana7030]

I Love the way you explain FILTER CONTEXT in this exercise this is High Level Congrats!!!

[@stormtooper13]

What a great class! You metrics are so elegant!

[@HowtoPowerBI]

Thank you so much!

[@jonathansmith31]

Great video, and perfect timing for acreport im working on. Would be great to see how this would work where the sales/forecast values are additive or a running total.

[@peterstone7630]

I’ve have had a great deal of satisfaction from using this metric. Thank you. Awesome instruction, methodology and explanations.

[@andrekroker420]

Your content is definitely among the best!

[@jafarhussain6606]

WOW what a great video and what a guy, clear explanation. Can you please make a video on Bill of Materials explosion?

[@xiaojunyip]

Thanks so much for this video. It's exactly what I am looking for to meet my work requirements. :)

[@brundleseth51]

Quick & Easy Video 👌... I have a question, in power bi how do I make a forecast?.. Not the automatic line that can be generated... But a column like in the video

[@shermansee]

You just helped me solved my prob! I couldn't get the line + dotted design likes yours until I realized I got the wrong measure & order.

[@HowtoPowerBI]

happy to hear it helped! 🙂👊

[@haydaar1000]

Great Video, just wondering if there is a solution to implement this feature to seperat between Actuals & Forecast for multiple Products for example so to include parameters into the legend.Thanks!!

[@yassirkhalid5376]

Great video and yes I do have a question! Is it possible to add two extra lines to the forecast, one upper and one lower? It will be great if you can add another video on this, because as you know usually forecast comes with confidence interval

[@HikimaruL2]

That is great solution very nice, my only problem I am getting is how to actually calculate my "forecast sales" measure. In my case, I am creating a dashboard that shows a Project, and how much the contractor was paid monthly. What I would like to do is to create a forecast measure that would project the average of all these payments done to a contractor to the future months until they reach the Total Contract amount. So for example, a project that has a total contract of $1,000,000, and the contractor was paid a total of $500,000 over 5 months (which means the average is $100,000/month). I would like to use this average for the next 5 months, in which is technically when the contractor would reach the full $1,000,000. But anyway, really nice video, I love your solutions.

[@HJ-bi9ec]

Hi! Thanks for the video!! But how can I do if I have only past data and want to show forecast using average of past 3 months of data?

[@AmdiSilword]

Thanks. Very good with the explanation when and why it works correctly.

[@HowtoPowerBI]

Great to hear! thx for watching Amdi! 😊😀

[@richujose]

Hi, I have a question, I have multiple forecast for multiple SKU's which I have generated through R and Python, now I want to use Power BI to visualize the Actuals plus Forecast. I will use a text filter to select the respective SKU's but would it be possible to visualize this in Power BI, if so how do I do that?

[@phamanhthu1596]

Thank you for a great tutorial! My issue is the latest sales date is in the middle of month 20/9 => when the forecast line start, it also start on September (but higher than the actual) => the logical is correct, but the visual is not i have a line of actual does not match T.T how to not handle this

[@Mansoorsadat]

Awesome tutorial BAS! You are awesome. Thank you

[@artemburov9456]

Hi Bas! Thanks for the video. Very simple and super informative.

[@HowtoPowerBI]

Glad you liked it and find it useful :)

[@saitenspieler3489]

Very useful, exactly what I needed. Thanks a lot!

[@telclissonperes]

Thank you so much for sharing expertise with us!!!! God bless you!

[@HowtoPowerBI]

:) thanks for your kind words

[@nawalbiyaye8609]

Great video, thank you, I was looking for it. How would you calculate the running total of “Sales and forcast” measure?

[@BrigadieruBrokastis]

What does the data model look like? Do you have 1 table with both forecast and actual values in it or do you have 2 tables - one for forecast and the other for the actuals?

[@telesforosotelogalindo3114]

Muchas gracias. saludos desde Cuernavaca México.

[@jaclynpugh4209]

Please explain what does removing filters in the first portion of your formula do? I like your solution, but need to learn more behind why and when to add or remove filters please. Thank you!

[@ymohamed3154]

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

[@pallavi_joseph]

you re a legend 😍😍😍😍😍😍😍 . You always make what i want .Thanks a lot

[@HowtoPowerBI]

Glad to hear that :):) reading your mind

[@captainciz]

great video but where are the forecast values coming from in the table? I saw very similar tutorial from SQLBI, but I had the same question. How do I generate forecast values? Do they come from a previous years sales data/ historic data? If anyone has the answer I would greatly appreciate it

[@HowtoPowerBI]

Normally, it is the estimates by the responsible teams (its usually a mix of historical growth and expected growth) 🙂

[@mariabardales]

@@HowtoPowerBI Hi Bas, your explanation is my clear, without exception it is still predetermined, also I see the video of SQLBI and you do not hear how it is the calculation of the Forecast column. Descargué el archivo que adjuntas, muchas gracias. Please note that this is a tabla that is established, without embargo, in the work we do not have manual estimates, we need to estimate with the same model, method or formula. What blades can you apply?

[@mrgilmour18]

It would be incredible if you could also combine budget as an extension to the ACTUALS… so actuals with a extension for forecast, and same actuals line with an extension for budget

[@harshamokhasi]

When we on the Tooltip, it shows both sales and forecast value. I want to see only sales data when the date is less than transaction date. can we do that?

[@abayomido]

Thanks for posting the content. However, If I wish to provide the about solution using a table/matrix chart, how can I use color to differentiate actual records and forecast record for conditional formatting?

[@koteswaribapatla8075]

Hi, your explanation is very clear .now i have one requirement i want to calculate forecast values based on latest month actuals and forecast value=(latest month actual/latest month num)+latest actual and i want to show if actual is not there for future months in line graph like how you shown in above video please provide me solution i had tried as explained in video but i am not getting future values.

[@Shalve94]

Thanks! Saved to watch later

[@HowtoPowerBI]

That's great! enjoy Shalva!

[@color007]

Thank you....great video !!

[@KārlisMežrozis]

Hi, thank you for the video. A quick question - as time goes on and the actual data come in and maybe some new future forecasts in the data table, will the same visual update automatically?

[@gauravbrham7869]

Thank you for sharing your skill 🙏

[@HowtoPowerBI]

Sure thing! I am really enjoying it :)

[@MTerkait]

I have made a similar solution but I still need the legend to show different actual/forecast for different products. any idea how to do it?

[@MandyHuynh-r7z]

Thank you! This is very helpful!

[@HowtoPowerBI]

Happy to hear you think so!

[@tibortot8148]

Thank you.

[@WarooatDK]

Great work!

[@Aquaelus]

Best Man on Earth

[@MrNald22]

If i'm not have data transaction forecast like in your video, but i want to know by history data transaction in YTD by Month periode of invoice

[@tomaskavicky]

excellent video

[@pcsalumar6497]

I appreciate it! How did calculate the Sales Forecast? From what i have seen you already had it calculated. In my case i want to do same thing, but i need to obtain forecast measure first.

[@jeffjohnson9686]

I would like to know this, too.

[@robbieausman1337]

This is great and very helpful, but why all this work when a quick calculated column would accomplish the same goal?

[@invisible2745]

Used this approach but tooltip show 2 values 1 for sales and 1 for Forcast in current and previous months, can you show only 1 value for current and previous months sales in tooltip?

[@samnad4972]

I would like to how you calculated the forcast , this video the forcast measure is already done. Please update.

[@rodrigolombana]

It's very usefull thank you very much !

[@txreal2]

Can you provide a link to download the data file so we can follow? Great video. Thanks.

[@HowtoPowerBI]

See in description

[@louism.4980]

Thank you for this! :)

[@oconnorra]

Thank you so much for explaining so clearly! I have a similar but different scenario with targets and actuals, but no date! I'm analysing how legal files pass through a legal process (more than 100 steps). I have the actual days each file takes to go through the legal steps, and the expected days it should take. This is fine, but how do I create a forecast of total days required from the current legal step to completion? Currently, I just have an index ([LegalSubStepIndex]). If I attempt your calculation, I get the first part right (with some tweaks), but not the second: Remaining Expected Steps = VAR LastLegalStep = CALCULATE( MAX( FactFiles[LegalSubStepIndex] ), FILTER( DimCurrentStep, DimCurrentStep[Legal Step] = "Current")) // this get me the current legal step of the file VAR RemainingLegalSteps = CALCULATE( [Expected Days], KEEPFILTERS( FactFiles[LegalSubStepIndex] >= LastLegalStep ) ) RETURN RemainingLegalSteps I get all the expected days for each step... :( Can you point me in the right direction?

[@milanmehra4009]

Dear Bas, You are too good. Thanks for your videos. One Query: It appears Sales Forecast is a column in your Table. What will be DAX if Sales Forecast is a Measure instead (Sales FCST = Sales of PY * What if selection growth %) ? Grateful for your guidance. Its not working for me perhaps because of this.

[@priyavadhanisivakumar2425]

Nicely explained 👌. Can I get to see sales forecast and relevant data model

[@huskyman1999]

Great video Bas! I modified this a bit to make it cumulative but I'm having an issue at the transition from actuals to forecast. The first value after the actuals end is the first forecast value, not the first forecast value plus the last actual value. Any ideas?

[@jasmin8486]

Hi I have a situation where monthly plan values to should change based on previous months actual, note the total plan for the year should be constant hence based on total actual till last date the Plan or forecast for remaining months should be derived based on logic where equal values will be allocated in remaining months (Total remaining forecasts divided by total remaining months)…Pls help

[@carneypwr]

But how did you do the forecasted sales?

[@barosmota2008]

Thank you for sharing this ! But how to do achieve the same thing when actual and forecast data are in the same table ?

[@barosmota2008]

Found a solution, and it was quite simple ... thanks a lot !

[@roxana-stefanaapetrei6109]

I have a problem with this. The forecast, although values lower than the actuals, goes higher on the axis than the actuals. Any ideas what's wrong?

[@amitwaze]

Hi, can we combine actual and forecast in power query or in power pivot?

[@HowtoPowerBI]

Hi Amit, also if you are using pq /pp in excel your could use a similar approach

[@amitwaze]

@@HowtoPowerBI really appreciate your reply. I am not sure how to declare variable and assign results into PQ/PP

[@sravankumar1767]

Nice explanation.....superb

[@HowtoPowerBI]

Thanks!

[@ajaysuresh8856]

awesome bro

[@HowtoPowerBI]

Thanks ✌️

[@kartik-wi2jv]

Awesome!!!!!! Thank for sharing..

[@HowtoPowerBI]

happy you liked it - thx Bala! 😀

[@FRANKWHITE1996]

thanks for sharing

[@annab2935]

Great video, very helpful. How can I do this with running totals?

[@HowtoPowerBI]

thanks Anna - that would work in a similar way. Just adjust the base measure to running total - see explanation here -> kzbin.info/www/bejne/eWrEcqKXmaibq80

[@annab2935]

@@HowtoPowerBI Thanks for that Bas - I have tried that but for some reason the actuals line is all ok it keeps going as a running total but once the line switches to the forecast it does not know to continue so the line dips down and starts again as a new running total. Any idea how I can fix that? It's been driving me mad. Thank you :)

[@spen2431]

💥 Say no more ! Thanks Bas 👍

[@HowtoPowerBI]

Any time! ;)

[@lexrobe_fly]

Danke!

[@lexrobe_fly]

Thanks for all your great efforts so far. Could already take a lot out of them through the last years actually, thanks! 👍

[@HowtoPowerBI]

thank you so much for the support! I'm very happy to hear it is useful for you, hopefully this continues :) have a nice day

[@koteswaribapatla8075]

Hi,how to create forecast column using actual column

[@cujo3097]

now how can I add another line for "Budget" and then get a variance to budget?

[@shrikantthakur7127]

Hi Please Explain Me How To create Sales Forecast Measure

[@Soulenergy31]

Amazing!!!!!!!!!!!!!!!!!!!!!!!!! Bas is the BOSS!!!!! :)

[@HowtoPowerBI]

haha thank you Saul!!!! 😊😄

[@ricardocontreras]

Gracias!!

[@HowtoPowerBI]

You are welcome Ricardo!

[@asad512]

Awesome, you rock.

[@HowtoPowerBI]

thx Asad!!!! 😊😎👊

[@fatherinms12]

can anyone point out to me to his video as to how he calculated the forecast. thanks

[@yannickeveille2063]

thanks, i would try to do the same !🤐

[@Chisanloius]

I sales Foreacts and Sales Actual a measure?

[@sravankumar1767]

I have one doubt how can we write dax measure for sales forecast

[@kanwalohri2526]

From where i can import raw data taken in the model like dim tables

[@HowtoPowerBI]

only the power bi file is available for download here, which you can find in the description

[@payalbhansali4695]

@@HowtoPowerBI There is no file in the description, can you please share the power BI file.

[@shyftyy]

OK, now you got me. Liked & subbed :)

[@HowtoPowerBI]

yey! awesome, thx for the sub Sander 😊👊

[@shyftyy]

@@HowtoPowerBI keep up the good work!

[@rgto777]

nice!

[@HowtoPowerBI]

Thanks Tiago!

[@TauTauTau1]

The download link row doesn’t have the file there. Just wanted to give a heads up.

[@HowtoPowerBI]

thx for letting me know, should be there now 🙂

[@brianligat9493]

Sorry, Bas. I'm struggling to see why this doesn't give the sum of the actual values AND the forecasts (e.g. 24894+25412 instead of just 25412. It's as if the Forecasts values are taking priority. Why doesn't the Actuals measure also need a FILTER based on the LastDate?

[@claudescarborough8533]

Instead of all of that work, why not created a 3rd column (as you did), and insert a simple IF statement that says that IF the Actual Column equals 0, then, pull the result from the Forecast Column, otherwise, pull the result from the Actual Column?

[@MTerkait]

You will have no data for 2024 so it's not zero.

[@pmajoros]

@@MTerkait COALESCE works for that, but using only that still gives wrong sums, so you have to mess with the filter environment

[@Kevrow88]

Hello! I love your videos and I am working on a project and would like to pay you for 30 min consultation if you are open to doing something as such. Even if you say no. I will continue to learn from your channel and drive people to check out your channel as the Dax Language is growing in demand at my job.

[@color007]

what table is dimDate? and so on....no explanation at all

[@HowtoPowerBI]

Here you go kzbin.info/www/bejne/g5mmZa2gZ8eseLM