98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Power BI training and help » Forecasting
Forecasting
Resolved · High Priority · Version 365
Mon 31 Oct 2022, 16:31Delegate Amy said...
Amy has attended:
Power BI Modelling, Visualisation and Publishing course
Forecasting
Can you use DAX to create a forecast on multiple columns rather than making a measure just for one column? E.g. forecast activity at month 12 for outpatients, inpatients, daycase patients).
Tue 1 Nov 2022, 16:53Delegate Amy said...
RE: Forecasting
Hello,
I basically have a table which current has YTD activity broken down by the type of patient and area (e.g. IP, OP and NF2F vs C&M, GM and Other). I want to show a forecast to month 12 for each of the values based on the previous years trend for the same time of year. How would I go about doing this please?
Thanks,
Kind Regards,
Amy
Wed 2 Nov 2022, 10:38Trainer Jens said...
RE: Forecasting
Hi Amy,
Thank you for the forum question.
As I mentioned on the DAX course, the DAX you need is depending on the data model you are using.
If you want to forecast each patient group (each column in a table), you will have to do DAX measures for each group (create the first measure. Copy and Paste and change the column name).
I found a link (see below), which also explain YTD forecast, but otherwise it is very similar to what we did on the course.
https://blog.enterprisedna.co/create-dynamic-forward-forecasts-in-power-bi-w-dax/
If you want to forecast from the trend, you will have to calculate the trend. It is complicated to calculate trend with DAX. We do not have a Trend function or a Forecast function as we have in Excel to do this simple.
Below you can find a video which explain trend in DAX.
https://www.youtube.com/watch?v=wWSdQ4FItkM
Most Power BI users take the average approach as we did on the course.
A walk around could be to connect Excel to the source data and then do the trend calculation in Excel, and then connect Power BI to the Excel file.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 2 Nov 2022, 10:58Delegate Amy said...
RE: Forecasting
Thanks Jens, this is really useful!
I was wondering whether there was a way to do the forecast in one calculation, so to include the forecast as a new row to the column rather than a new table with the forecast calcs included. I will do the latter though.
Thanks for your help and useful links! Really enjoyed the training and your enthusiasm :)
Kind Regards,
Amy
Wed 2 Nov 2022, 11:15
Edited on Wed 2 Nov 2022, 11:18Trainer Jens said...
RE: Forecasting
Hi Amy,
As far as I understand, then you have a column for each patient group. I am sorry to say it, but it is wrong. It is not list format structure.
You should have 1 column with the heading "Patient group" and then have listed all the different patient groups down in the same column.
Then you would only have needed one forecast measure then the visuals would brake down the numbers for each patient group from one measure.
Just to try to explain by using the data model on the course.
In tblSales (all the sales transactions) we had a column with the countries. If I had a column for each country with the quantity, the DAX would have been a nightmare and it would have been impossible to visualise sales per country.
I do not know how your data is structured, but I am pretty confident that the query in Power BI can structure your data correct.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 2 Nov 2022, 10:38Trainer Jens said...
RE: Forecasting
Hi Amy,
Thank you for the forum question.
As I mentioned on the DAX course, the DAX you need is depending on the data model you are using.
If you want to forecast each patient group (each column in a table), you will have to do DAX measures for each group (create the first measure. Copy and Paste and change the column name).
I found a link (see below), which also explain YTD forecast, but otherwise it is very similar to what we did on the course.
https://blog.enterprisedna.co/create-dynamic-forward-forecasts-in-power-bi-w-dax/
If you want to forecast from the trend, you will have to calculate the trend. It is complicated to calculate trend with DAX. We do not have a Trend function or a Forecast function as we have in Excel to do this simple.
Below you can find a video which explain trend in DAX.
https://www.youtube.com/watch?v=wWSdQ4FItkM
Most Power BI users take the average approach as we did on the course.
A walk around could be to connect Excel to the source data and then do the trend calculation in Excel, and then connect Power BI to the Excel file.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 2 Nov 2022, 11:41Delegate Amy said...
RE: Forecasting
Hi Jens,
Thanks for getting back to me.
Sorry, I think I explained this poorly.
I do not have columns saying the patient type across the top and then the values, e.g. the activity.
I have one column which says the area, another column which says the total attends, another column which says whether the patient is an inpatient or outpatient, and another column which says the type of procedure (e.g. F2F, 1st appointment etc).
I have then calculated measures to say the YTD actual figures for each of the data points I want to show (total attends, attends for IP appointments, attends for 1st OP appointments, attends for NF2F et).
I then wanted to show a forecast to month 12 (so the rest of the financial year) for the same data points. This would take the YTD figures that have been calculated in the measures, and then add on the trend of previous years.
Does that make more sense?
Apologies for confusion. The data might still be wrong, as we explained in the meeting, the data quality is normally quite poor.
Kind Regards,
Amy
Wed 2 Nov 2022, 11:52Trainer Jens said...
RE: Forecasting
Thanks Amy
Good then your data are structured right.
Yes it makes sense.
It is me who should say sorry. Based on the last message from you, I read your question again. You said that you want to calculate forecast for more columns. I misunderstood it. I read it as you had one column for each patient group.
Sorry Amy.
Good luck with your forecast and please let us know if we can provide you with more help.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 2 Nov 2022, 12:12Delegate Amy said...
RE: Forecasting
Good to hear that the data is formatted correctly - phew! :)
Thanks Jens, I will do. Thanks for such a quick reply as well. This is my first time using the platform and i'm really impressed.
Have a great day!
Kind Regards,
Amy
Wed 2 Nov 2022, 15:18Trainer Jens said...
RE: Forecasting
Thanks Amy,
It is a pleasure to be helpful.
The link below is a very nice video and not too complicated. It explains trend analysis in DAX and forecast based on trend.
https://www.youtube.com/watch?v=fk9LRvZYdvE
And great day to you
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Power BI tip:Scheduled Refresh and Power BI ServiceIf you're using Power BI Service, set up scheduled refresh for your datasets. This ensures that your reports are always up-to-date with the latest data. Understand the refresh limits imposed by Power BI Service and optimize your data model and queries to stay within those constraints. |
