How to forecast data using DAX in Power BI? | Forecast Data in Power BI | DAX | BI Consulting Pro
Рет қаралды 36,217
Күн бұрынIn this video, I have explained a method that you can use to "forecast data in Power BI" using DAX.
Related Link:
********* DA-100 Questions - Answers ***********
1drv.ms/b/s!ApIf9f9Q3qOxkyMFR...
1drv.ms/b/s!ApIf9f9Q3qOxkyQrM...
**********Important Links **********
* For the DA-100 Exam Tutorial Series: • PL- 300 Exam Tutorial
* For more DAX Videos: • DAX Sundays
* For more Weekly Videos: • Power BI Weekly Videos
****************************************
1. Power BI Crash Course: • How to get started wit...
2. How to pass DA-100 Exam: • How to pass DA-100/PL-...
3. Data Classification in Power BI: • Should you go for Powe...
4. Sensitivity Labels in Power B: • What are Sensitivity L...
5. What is Power BI Service? • What is Power BI Service?
6. Power BI Data Model Analysis: • How to do data analysi...
7. Filter Propagation Concept: • What is Filter Propaga...
8. Dynamic Page Navigation with RLS Video: • How to Navigate Pages ...
9. Incremental Refresh in Power BI: • What is Incremental Re...
10. Create Date Table in Power BI: • 2 Ways to Create Simpl...
****************************************
☎️ LET'S CONNECT!
****************************************
🐥 Twitter: / biconsultingpr1
☞ Instagram: / biconsultingpro
➥ Facebook: / biconsultingpro
🧑💻 Website: www.biconsultingpro.com
#forecast #DAX #PowerBI #DAXSundays
~-~~-~~~-~~-~
Please watch: "Microsoft Azure Synapse Analytics Tutorial | Azure Synapse Studio |BI Consulting Pro |Azure Tutorial"
• Microsoft Azure Synaps...
~-~~-~~~-~~-~
@BIConsultingPro 3 жыл бұрын
You can download .pbix file from here: 1drv.ms/u/s!ApIf9f9Q3qOxlEaFBf_KoEYzKWES?e=w87h7Q
@mthokozisithandolwethumthe8461 3 жыл бұрын
WoW - I'm still new to PowerBi and I had no idea that this was possible. Much appreciated man
@BIConsultingPro 3 жыл бұрын
You're welcome!
@komanguy 3 жыл бұрын
Thanks for this video! Thanks for the LASTNONBLANK and FIRSTNONBLANK functions too!
@rutujanagare741 Жыл бұрын
Pls keep posting such type of vdos , very useful..Thanks alot !
@bpremchand93 3 жыл бұрын
Thank you so much for the DA-100 tutorial videos, that helped me to complete my certification today. Many Thanks :)
@BIConsultingPro 3 жыл бұрын
Wow! I am glad to hear! Congratulations :)
@skinnyulam6801 Жыл бұрын
Just amazing!
@smohammadeliyaz134 2 жыл бұрын
Awesome! This is really helpful. Keep posting. Thank you.
@smohammadeliyaz134 2 жыл бұрын
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
@abhinayrozer7379 3 жыл бұрын
Much informative.. thankyou man
@BIConsultingPro 3 жыл бұрын
You're welcome!
@Arkngthunchsturdumz 3 жыл бұрын
Very useful, thank you
@BIConsultingPro 3 жыл бұрын
You're welcome!!
@sushmak7867 7 ай бұрын
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
@SuperNeha1983 11 ай бұрын
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.
@gilbertosantos2503 2 жыл бұрын
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.
@saisalian5500 2 жыл бұрын
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
@gambu4810 2 жыл бұрын
Me too please 🙏🙏🙏
@BIConsultingPro Жыл бұрын
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!!
@BIConsultingPro Жыл бұрын
@@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!!
@BIConsultingPro Жыл бұрын
@@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!!
@arnoldtagne4641 11 ай бұрын
Thanks for sharing. I tried it but I get zero all over the years for CAGR
@juanignacioXI 2 жыл бұрын
Great video, very useful. Can it be done with months?
@BIConsultingPro 2 жыл бұрын
I’ll suggest you to try first!
@bukunmiadebanjo9684 11 ай бұрын
Great content @Bi. Please is this applicable to categorical dataset. For example forecasting sales for Furniture, Clothing and Household items.
@BIConsultingPro 11 ай бұрын
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.
@syedabthahir4511 2 жыл бұрын
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
@02reeta 3 жыл бұрын
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
@BIConsultingPro 3 жыл бұрын
You need to create a resume that can demonstrate your skills in Power BI, SQL area!
@jafarhussain4665 9 күн бұрын
Hi, Thanks for Uploading this Video. Can you please make a video for Monthly Forecast as well?? Thank you
@BIConsultingPro 4 күн бұрын
Yes, soon
@angelmaravilla4708 Жыл бұрын
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?
@BIConsultingPro Жыл бұрын
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() )
@DonVictron 3 жыл бұрын
Can I use this method without grouping, so I can retail all other slicer fields?
@BIConsultingPro 3 жыл бұрын
Give it a try, and let me know your findings! We shouldn't hesitate trying. We always learn something.
@yashjk899 2 жыл бұрын
Hii This forecast is useful when our raw data is gradually increase. But if any fluctuation then?
@BIConsultingPro 2 жыл бұрын
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.
@jayantmishra4270 Жыл бұрын
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 ??
@BIConsultingPro Жыл бұрын
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.
@anjandas9269 2 жыл бұрын
I have Date Dim Table, i.e. Calendar Auto. I am choosing "Date Dim[Year]". It it correct ?
@BIConsultingPro 2 жыл бұрын
Yes, you can use it but make sure you have the correct data model.
@rogeriopsvalle 2 жыл бұрын
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.
@BIConsultingPro 2 жыл бұрын
I don't! I can have a look into this but currently, I cannot promise as I am swamped with my work.
@rogeriopsvalle 2 жыл бұрын
@@BIConsultingPro I appreciate your attention. I will look forward to your answer. Regards.
@Emerikled 2 жыл бұрын
How to do the same idea but with quarters?
@BIConsultingPro 2 жыл бұрын
I haven't figure it out yet. However, I promise, I'll look into this.
@bhagirathiramesh1891 2 ай бұрын
is there a way that stakeholders can enter Forecast length instead of us configuring it?
@BIConsultingPro Ай бұрын
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.
@rutujanagare741 Жыл бұрын
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
@BIConsultingPro Жыл бұрын
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.
@fw4693 8 ай бұрын
Please which method statistique do u use ? Ans how do u calculate saisonality ?
@BIConsultingPro 7 ай бұрын
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.
@pingi014 10 ай бұрын
Unfortunately, my forecast DAX formula came out as blank :(
@anjandas9269 2 жыл бұрын
When I going to calculate the forecast, it is showing an error . CAGS power function is not correct.
@BIConsultingPro 2 жыл бұрын
Please try again. It's working fine.
@AwesomeEndings 28 күн бұрын
Is it same way for Monthly forcast
@BIConsultingPro 26 күн бұрын
Yes but using DAX logic rather than using Power BI Desktop app
@Travelwithus304 3 жыл бұрын
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.
@BIConsultingPro 3 жыл бұрын
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!!
@Travelwithus304 3 жыл бұрын
Thank you Sir for your motivation and if I will stuck, your videoes are there☺️I will learn more while working also☺️
@pthapa55 8 ай бұрын
When I followed your example, mine forecast is coming nothing at all. What could be the issue?
@BIConsultingPro 7 ай бұрын
Honestly, I am not sure. Can you share your .pbix file with me at connect@biconsultingpro.com?
@pthapa55 7 ай бұрын
I have sent the sample dataset in the inbox.@@BIConsultingPro
@abbabccbcddc Жыл бұрын
can this be done with bar chart?
@BIConsultingPro 11 ай бұрын
Bar chart is for categories and forecast needs a timeline.
@siridinath Жыл бұрын
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
@BIConsultingPro Жыл бұрын
Did you try the code as it is?
@siridinath Жыл бұрын
@@BIConsultingPro ya
@MGleb-tp1kr Жыл бұрын
You've put last year and first into brackets (CALCULATE(SUM('Data'[Quantity]),'Data'[Year] = LastYear(here!), remove them and formula will be working
@israelrangel6063 Жыл бұрын
hehe, and what's DAX?
@BIConsultingPro Жыл бұрын
Please check our DAX tutorial
@fahadyousuf6398 Жыл бұрын
Thank You, such a helpful video, but can this method be used if I want to do month-wise forecasting, please reply ???
@BIConsultingPro Жыл бұрын
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.
BI Consulting Pro
Рет қаралды 1,7 М.
How to Power BI
Рет қаралды 47 М.
Adam Finer - Learn BI Online
Рет қаралды 27 М.
How to Power BI
Рет қаралды 48 М.