That's great solution - thanks for sharing it. How to do this forecasting when we have a sales data by monthly timing and it needs to calculate the forecast based on MAT? Many thanks for your tips. All best.
I am also looking for similar solution. Need a measure to create forecast with growth percentage prediction from industry data and some correction from previous months data. If you can help would be great. Cheers
To modify the given DAX code for forecasting based on Monthly Aggregated Total (MAT), you can make the following adjustments: Forecast = VAR Lastyear = [Last Year] VAR No_of_years = SELECTEDVALUE('Year Table'[Year]) - Lastyear RETURN IF ( SELECTEDVALUE('Year Table'[Year]) > Lastyear, CALCULATE( SUM('Sales Table'[Sales]), 'Year Table'[Year] = Lastyear ) * POWER((1 + [CAGR]), No_of_years), BLANK() ) I hope this will help you. Don't forget to share your feedback!!
@@saisalian5500 To modify the given DAX code for forecasting based on Monthly Aggregated Total (MAT), you can make the following adjustments: Forecast = VAR Lastyear = [Last Year] VAR No_of_years = SELECTEDVALUE('Year Table'[Year]) - Lastyear RETURN IF ( SELECTEDVALUE('Year Table'[Year]) > Lastyear, CALCULATE( SUM('Sales Table'[Sales]), 'Year Table'[Year] = Lastyear ) * POWER((1 + [CAGR]), No_of_years), BLANK() ) I hope this will help you. Don't forget to share your feedback!!
@@gambu4810 To modify the given DAX code for forecasting based on Monthly Aggregated Total (MAT), you can make the following adjustments: Forecast = VAR Lastyear = [Last Year] VAR No_of_years = SELECTEDVALUE('Year Table'[Year]) - Lastyear RETURN IF ( SELECTEDVALUE('Year Table'[Year]) > Lastyear, CALCULATE( SUM('Sales Table'[Sales]), 'Year Table'[Year] = Lastyear ) * POWER((1 + [CAGR]), No_of_years), BLANK() ) I hope this will help you. Don't forget to share your feedback!!
Your Videos are really amazing, I am really new to power bi learning from Udemy, but from you, I am extracting new thing which is not mentioned in my course, Thanks I am following your videos, my background in digital marketing, now want to switch Power bi, how can I get a job any help?? Thanks in Advance. Reeta
Sir I have a question,I learnt power bi by self,and I followed your videoes,I have total experience of 2 years 8 months,I gave multiple interviews and I am selected in one of the best company as a PowerBi Developer, Thanks a lot sir but will it make any problem for me as I don't have any real life project experience,I learnt it by self,practiced by self,made 2 to 3 dashboards and I am learning till now.
If you have learned it well then there won't be any problem. Also, remember, the best experience comes from on job training so don't worry, everything is going to be all right! By the way, congratulations!!
Man!! This is freacking amaazing!!, only one doubt: How can you change the code, if we want that the CAGR change dynamicaly with a slicer?? I mean, the last code calculates the CAGR taking in consideration ALL the years with sales (lets say "2015 to "2022"), but when with a slicer I select another range (let's say "2018 to 2022"), the ratio doesn't change... Do you know what changes must be done on the DAX code?
how to apply formulas for forecasting values. For example, i have annual premium but want to apply premium rate % to policies after current date and calculate future premium.
You can do it via three steps: 1) Create a date table with a column containing all the months you want to forecast. 2) Add the historical data to your Power BI report, including a column with the corresponding month for each data point. 3) Create a measure to calculate the total sales for each month in the historical data. For example, if your sales data is in a table called 'Sales', and your date column is called 'Date', the measure might look like this: Total Sales = SUM(Sales[Sales Amount]) 4) Create a measure to calculate the monthly growth rate for each month. This can be done using the following formula: Monthly Growth Rate = DIVIDE( [Total Sales], CALCULATE( [Total Sales], DATEADD('Date'[Date], -1, MONTH) ) ) - 1 5) Finally, create a measure to forecast the sales for each month in the future. This can be done using the following formula: Forecast Sales = [Total Sales] * (1 + [Monthly Growth Rate]) This formula uses the [Total Sales] and [Monthly Growth Rate] measures to calculate the forecasted sales for each month. To visualize the forecast, you can create a line chart with the date column on the x-axis, and the [Total Sales] and [Forecast Sales] measures on the y-axis. This will show the historical sales data as well as the forecasted sales for each month.
I have a question, I would like to add multiple conditions in the forecast values. When I tried to add conditions in the CALCULATE(SUM(measure), Year table[year]= Lastyear, 'Sales table'[Location]= "London", 'Sales table'[Product]="Furniture" I am not getting the correct results. Your help is much appreciated. Thank you
Hi, thank you for your feedback. You can try it and share your feedback. In order to see forecast, you should have a timeline so I don't think this will work for categories. However, if you want any option, please do share with us too.
In some paper they have asked Which is not a forecasting technique Regression Time series HOLT & WINTER method Exponential smoothing Which option I need to select ??
Regression is not a forecasting technique in the context of time series forecasting. While regression analysis is a valuable statistical method for understanding relationships between variables, it is not a standalone technique for time series forecasting. Time series techniques like Holt-Winters method and exponential smoothing are more suitable for forecasting when dealing with time series data.
You may would like to read here: www.thebiccountant.com/2022/05/29/forecasting-with-seasonality-in-power-bi/ Let me know if that helps otherwise I’ll explain you in detail later.
probably, we need to change logic then. However, this should work. It all depends on your logic of forecasting. You can try to change it as per your situation.
Hi sir looking for this solution past 3 month. But in my case instead of last years can we forecast based on last 3 average days value until reaching the goals. Example Total 1000 pcs Done 450 pcs and my last 3 days average 45 pcs. I need the dotted curve continuation of actual curve until to reach 1000 by required working days... is it possible thanks in advance
You have made Forecasting on the basis of Year and I want to make on the basis of Month , so i have created formula accordingly that , but still its not working , pls suggest
Mr. I hope this message finds you well. Do you have by any chance, tips or Works related to criation of S Curve with primavera P6 conected to SQL DB If you could share I would aprecciated. Thanks in advance Regards.
You can download .pbix file from here: 1drv.ms/u/s!ApIf9f9Q3qOxlEaFBf_KoEYzKWES?e=w87h7Q
WoW - I'm still new to PowerBi and I had no idea that this was possible. Much appreciated man
You're welcome!
Thank you so much for the DA-100 tutorial videos, that helped me to complete my certification today. Many Thanks :)
Wow! I am glad to hear! Congratulations :)
Pls keep posting such type of vdos , very useful..Thanks alot !
Thanks for this video! Thanks for the LASTNONBLANK and FIRSTNONBLANK functions too!
That's great solution - thanks for sharing it. How to do this forecasting when we have a sales data by monthly timing and it needs to calculate the forecast based on MAT? Many thanks for your tips. All best.
I am also looking for similar solution. Need a measure to create forecast with growth percentage prediction from industry data and some correction from previous months data. If you can help would be great. Cheers
Me too please 🙏🙏🙏
To modify the given DAX code for forecasting based on Monthly Aggregated Total (MAT), you can make the following adjustments:
Forecast =
VAR Lastyear = [Last Year]
VAR No_of_years = SELECTEDVALUE('Year Table'[Year]) - Lastyear
RETURN
IF (
SELECTEDVALUE('Year Table'[Year]) > Lastyear,
CALCULATE(
SUM('Sales Table'[Sales]),
'Year Table'[Year] = Lastyear
) * POWER((1 + [CAGR]), No_of_years),
BLANK()
)
I hope this will help you. Don't forget to share your feedback!!
@@saisalian5500
To modify the given DAX code for forecasting based on Monthly Aggregated Total (MAT), you can make the following adjustments:
Forecast =
VAR Lastyear = [Last Year]
VAR No_of_years = SELECTEDVALUE('Year Table'[Year]) - Lastyear
RETURN
IF (
SELECTEDVALUE('Year Table'[Year]) > Lastyear,
CALCULATE(
SUM('Sales Table'[Sales]),
'Year Table'[Year] = Lastyear
) * POWER((1 + [CAGR]), No_of_years),
BLANK()
)
I hope this will help you. Don't forget to share your feedback!!
@@gambu4810
To modify the given DAX code for forecasting based on Monthly Aggregated Total (MAT), you can make the following adjustments:
Forecast =
VAR Lastyear = [Last Year]
VAR No_of_years = SELECTEDVALUE('Year Table'[Year]) - Lastyear
RETURN
IF (
SELECTEDVALUE('Year Table'[Year]) > Lastyear,
CALCULATE(
SUM('Sales Table'[Sales]),
'Year Table'[Year] = Lastyear
) * POWER((1 + [CAGR]), No_of_years),
BLANK()
)
I hope this will help you. Don't forget to share your feedback!!
Thanks for sharing.
I tried it but I get zero all over the years for CAGR
Hi, video is really awesome...in the line chart is it possible to group 2014 ,2016 values and show as a single value and followed by forecast
Nice job and very interesting
Glad you enjoyed it
Just amazing!
Very useful, thank you
You're welcome!!
Your Videos are really amazing, I am really new to power bi learning from Udemy, but from you, I am extracting new thing which is not mentioned in my course, Thanks I am following your videos, my background in digital marketing, now want to switch Power bi, how can I get a job any help?? Thanks in Advance. Reeta
You need to create a resume that can demonstrate your skills in Power BI, SQL area!
Hi, Thanks for Uploading this Video.
Can you please make a video for Monthly Forecast as well??
Thank you
Yes, soon
Sir I have a question,I learnt power bi by self,and I followed your videoes,I have total experience of 2 years 8 months,I gave multiple interviews and I am selected in one of the best company as a PowerBi Developer, Thanks a lot sir but will it make any problem for me as I don't have any real life project experience,I learnt it by self,practiced by self,made 2 to 3 dashboards and I am learning till now.
If you have learned it well then there won't be any problem. Also, remember, the best experience comes from on job training so don't worry, everything is going to be all right! By the way, congratulations!!
Thank you Sir for your motivation and if I will stuck, your videoes are there☺️I will learn more while working also☺️
Man!! This is freacking amaazing!!, only one doubt:
How can you change the code, if we want that the CAGR change dynamicaly with a slicer??
I mean, the last code calculates the CAGR taking in consideration ALL the years with sales (lets say "2015 to "2022"), but when with a slicer I select another range (let's say "2018 to 2022"), the ratio doesn't change...
Do you know what changes must be done on the DAX code?
Try the following DAX Code:
Forecast =
VAR Lastyear = [Last Year]
VAR No_of_years = SELECTEDVALUE('Year Table'[Year]) - Lastyear
VAR DynamicCAGR = SELECTEDVALUE('CAGR Slicer'[CAGR])
RETURN
IF (
SELECTEDVALUE('Year Table'[Year]) > Lastyear,
CALCULATE(
SUM('Sales Table'[Sales]),
'Year Table'[Year] = Lastyear
) * POWER((1 + DynamicCAGR), No_of_years),
BLANK()
)
Much informative.. thankyou man
You're welcome!
how to apply formulas for forecasting values. For example, i have annual premium but want to apply premium rate % to policies after current date and calculate future premium.
Thank You, such a helpful video, but can this method be used if I want to do month-wise forecasting, please reply ???
You can do it via three steps:
1) Create a date table with a column containing all the months you want to forecast.
2) Add the historical data to your Power BI report, including a column with the corresponding month for each data point.
3) Create a measure to calculate the total sales for each month in the historical data. For example, if your sales data is in a table called 'Sales', and your date column is called 'Date', the measure might look like this:
Total Sales = SUM(Sales[Sales Amount])
4) Create a measure to calculate the monthly growth rate for each month. This can be done using the following formula:
Monthly Growth Rate = DIVIDE(
[Total Sales],
CALCULATE(
[Total Sales],
DATEADD('Date'[Date], -1, MONTH)
)
) - 1
5) Finally, create a measure to forecast the sales for each month in the future. This can be done using the following formula:
Forecast Sales = [Total Sales] * (1 + [Monthly Growth Rate])
This formula uses the [Total Sales] and [Monthly Growth Rate] measures to calculate the forecasted sales for each month.
To visualize the forecast, you can create a line chart with the date column on the x-axis, and the [Total Sales] and [Forecast Sales] measures on the y-axis. This will show the historical sales data as well as the forecasted sales for each month.
Great video, very useful. Can it be done with months?
I’ll suggest you to try first!
Awesome! This is really helpful. Keep posting. Thank you.
I have a question, I would like to add multiple conditions in the forecast values.
When I tried to add conditions in the CALCULATE(SUM(measure),
Year table[year]= Lastyear,
'Sales table'[Location]= "London",
'Sales table'[Product]="Furniture"
I am not getting the correct results.
Your help is much appreciated.
Thank you
Great content @Bi. Please is this applicable to categorical dataset. For example forecasting sales for Furniture, Clothing and Household items.
Hi, thank you for your feedback. You can try it and share your feedback. In order to see forecast, you should have a timeline so I don't think this will work for categories. However, if you want any option, please do share with us too.
In some paper they have asked
Which is not a forecasting technique
Regression
Time series
HOLT & WINTER method
Exponential smoothing
Which option I need to select ??
Regression is not a forecasting technique in the context of time series forecasting.
While regression analysis is a valuable statistical method for understanding relationships between variables, it is not a standalone technique for time series forecasting. Time series techniques like Holt-Winters method and exponential smoothing are more suitable for forecasting when dealing with time series data.
Please which method statistique do u use ? Ans how do u calculate saisonality ?
You may would like to read here: www.thebiccountant.com/2022/05/29/forecasting-with-seasonality-in-power-bi/
Let me know if that helps otherwise I’ll explain you in detail later.
is there a way that stakeholders can enter Forecast length instead of us configuring it?
In Power BI, you can't use it but using Power Apps in Power BI, you can try. This will allow you to enter it as a parameter.
Hii
This forecast is useful when our raw data is gradually increase.
But if any fluctuation then?
probably, we need to change logic then. However, this should work. It all depends on your logic of forecasting. You can try to change it as per your situation.
When I followed your example, mine forecast is coming nothing at all. What could be the issue?
Honestly, I am not sure. Can you share your .pbix file with me at connect@biconsultingpro.com?
I have sent the sample dataset in the inbox.@@BIConsultingPro
How to do the same idea but with quarters?
I haven't figure it out yet. However, I promise, I'll look into this.
Is it same way for Monthly forcast
Yes but using DAX logic rather than using Power BI Desktop app
Can I use this method without grouping, so I can retail all other slicer fields?
Give it a try, and let me know your findings! We shouldn't hesitate trying. We always learn something.
I have Date Dim Table, i.e. Calendar Auto. I am choosing "Date Dim[Year]". It it correct ?
Yes, you can use it but make sure you have the correct data model.
Hi sir looking for this solution past 3 month. But in my case instead of last years can we forecast based on last 3 average days value until reaching the goals.
Example
Total 1000 pcs
Done 450 pcs and my last 3 days average 45 pcs. I need the dotted curve continuation of actual curve until to reach 1000 by required working days... is it possible thanks in advance
Unfortunately, my forecast DAX formula came out as blank :(
You have made Forecasting on the basis of Year and I want to make on the basis of Month , so i have created formula accordingly that , but still its not working , pls suggest
Hi, Thanks for your reply. Although, I haven't tried your case but I'll have a look once I get sometime, and will get back to you.
Mr.
I hope this message finds you well.
Do you have by any chance, tips or Works related to criation of S Curve with primavera P6 conected to SQL DB
If you could share I would aprecciated.
Thanks in advance
Regards.
I don't! I can have a look into this but currently, I cannot promise as I am swamped with my work.
@@BIConsultingPro I appreciate your attention. I will look forward to your answer.
Regards.
can this be done with bar chart?
Bar chart is for categories and forecast needs a timeline.
When I going to calculate the forecast, it is showing an error . CAGS power function is not correct.
Please try again. It's working fine.
I have tried it but is it is showing error
a function placeholder has been used in a true or false which is not allowed
Did you try the code as it is?
@@BIConsultingPro ya
You've put last year and first into brackets (CALCULATE(SUM('Data'[Quantity]),'Data'[Year] = LastYear(here!), remove them and formula will be working
hehe, and what's DAX?
Please check our DAX tutorial