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.
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
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!
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
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.
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!!
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?
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?
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.
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?
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.
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!
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
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?
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?
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.
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.
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?
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 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?
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?
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?
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
thanks Anna - that would work in a similar way. Just adjust the base measure to running total - see explanation here -> ua-cam.com/video/C9aAoadChuk/v-deo.html
@@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 :)
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
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?
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?
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.
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.
I appreciate that! thank you Rajneesh! 😊
You are just born for this. Your tutorials are really at a high level.
Incredible video!Very elaborative and i deeply cherished the walkthrough on the DAX how-to's with different approaches! Keep up the great work!!
Your way of teaching is so simple and up to the mark. :)
Glad you think so!
I've been searching for this video/topic for over the past 3 years.
i'm crying from joy 😂
Thank you!!!
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
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!
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
Dude.... this just helped me to solve a problem. Thank you Bas!
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.
Thank you so much for explaining the Dax in details. This video has helped in getting the exact same output. Excellent knowledge
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!!
This is very helpful! Thank you! Concise explanations with different ways to look at the problem with what the results are.
I Love the way you explain FILTER CONTEXT in this exercise this is High Level Congrats!!!
What a great class! You metrics are so elegant!
Thank you so much!
Great video, thank you, I was looking for it.
How would you calculate the running total of “Sales and forcast” measure?
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?
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?
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?
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.
Your content is definitely among the best!
I’ve have had a great deal of satisfaction from using this metric. Thank you. Awesome instruction, methodology and explanations.
WOW what a great video and what a guy, clear explanation. Can you please make a video on Bill of Materials explosion?
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?
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.
Thanks so much for this video. It's exactly what I am looking for to meet my work requirements. :)
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!
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
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.
happy to hear it helped! 🙂👊
Hi Bas! Thanks for the video. Very simple and super informative.
Glad you liked it and find it useful :)
Thanks. Very good with the explanation when and why it works correctly.
Great to hear! thx for watching Amdi! 😊😀
Can you provide a link to download the data file so we can follow? Great video. Thanks.
See in description
Awesome tutorial BAS! You are awesome. Thank you
Very useful, exactly what I needed. Thanks a lot!
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?
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?
Thank you so much for sharing expertise with us!!!! God bless you!
:) thanks for your kind words
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.
I would like to know this, too.
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.
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?
Great video. Can you please make us a video of how you calculated the forecast. Thanks
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
Normally, it is the estimates by the responsible teams (its usually a mix of historical growth and expected growth) 🙂
@@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?
This is great and very helpful, but why all this work when a quick calculated column would accomplish the same goal?
Nicely explained 👌. Can I get to see sales forecast and relevant data model
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?
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?
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?
Thanks! Saved to watch later
That's great! enjoy Shalva!
Thank you for sharing this !
But how to do achieve the same thing when actual and forecast data are in the same table ?
Found a solution, and it was quite simple ... thanks a lot !
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
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
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?
you re a legend 😍😍😍😍😍😍😍 . You always make what i want .Thanks a lot
Glad to hear that :):) reading your mind
Muchas gracias. saludos desde Cuernavaca México.
I would like to how you calculated the forcast , this video the forcast measure is already done. Please update.
Thank you! This is very helpful!
Happy to hear you think so!
Thank you....great video !!
Thank you for sharing your skill 🙏
Sure thing! I am really enjoying it :)
Great video, very helpful. How can I do this with running totals?
thanks Anna - that would work in a similar way. Just adjust the base measure to running total - see explanation here -> ua-cam.com/video/C9aAoadChuk/v-deo.html
@@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 :)
Thank you for this! :)
But how did you do the forecasted sales?
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
Great work!
Best Man on Earth
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?
excellent video
It's very usefull thank you very much !
Hi, can we combine actual and forecast in power query or in power pivot?
Hi Amit, also if you are using pq /pp in excel your could use a similar approach
@@HowtoPowerBI really appreciate your reply. I am not sure how to declare variable and assign results into PQ/PP
now how can I add another line for "Budget" and then get a variance to budget?
Thank you.
Hi,how to create forecast column using actual column
Hi Please Explain Me How To create Sales Forecast Measure
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?
You will have no data for 2024 so it's not zero.
@@MTerkait COALESCE works for that, but using only that still gives wrong sums, so you have to mess with the filter environment
I sales Foreacts and Sales Actual a measure?
Nice explanation.....superb
Thanks!
can anyone point out to me to his video as to how he calculated the forecast. thanks
I have one doubt how can we write dax measure for sales forecast
💥 Say no more ! Thanks Bas 👍
Any time! ;)
Awesome!!!!!! Thank for sharing..
happy you liked it - thx Bala! 😀
thanks for sharing
The download link row doesn’t have the file there. Just wanted to give a heads up.
thx for letting me know, should be there now 🙂
awesome bro
Thanks ✌️
Gracias!!
You are welcome Ricardo!
From where i can import raw data taken in the model like dim tables
only the power bi file is available for download here, which you can find in the description
@@HowtoPowerBI There is no file in the description, can you please share the power BI file.
Amazing!!!!!!!!!!!!!!!!!!!!!!!!! Bas is the BOSS!!!!! :)
haha thank you Saul!!!! 😊😄
Danke!
Thanks for all your great efforts so far. Could already take a lot out of them through the last years actually, thanks! 👍
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
OK, now you got me. Liked & subbed :)
yey! awesome, thx for the sub Sander 😊👊
@@HowtoPowerBI keep up the good work!
Awesome, you rock.
thx Asad!!!! 😊😎👊
nice!
Thanks Tiago!
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.
thanks, i would try to do the same !🤐
what table is dimDate? and so on....no explanation at all
Here you go
ua-cam.com/video/MhC4zj2byBQ/v-deo.html