Budget Forecasting in Power BI using Linear Regression: Manager's Guide

hello hello and welcome to the introverted manager today we'll be discussing my favorite topic powerbi and how to predict the future with linear regression let's Dive Right In let's take a second and talk about what linear regression is and where does it apply it usually used for business forecasting it can be used to predict future Sales Inventory requirements or human Trends based on historical data although there are some limitations if your data has some seasonality or outliers you should probably explore other techniques linear regression will be affected significantly by the outliers and therefore you should probably not rely on what it shows you there are two variables X and Y independent one and dependent one one let's say in our case we have X AIS which is time serious data and we have y variable which changes with time let's say you have multiple data points on your graph what linear regression line will be is the best Feit in straight line through your data points on the graph this line is mathematically calculated to minimize the distance between those data points and your your slope is an angle of that line if your y variable grows your slope will be angle will be positive if it's the other case it will be negative okay let's see what we'll be working with today you might be familiar with this report already because I showed it multiple times in weekly review videos this is the report where I'm tracking my expenses how and how I am doing in terms of the budget as you as you might remember I have specific budget for a year which is 20K and you would like to be on top of that and see how I'm burden it and whether I am on track or I need to cut my expenses there are three lines on this visual the gray one is Baseline burndown the dark blue one is my actual burn and there is new one which is purplish color which is linear regression based on my running burndown let's see what it shows if I un zooom so the green line is BAS line and the purplish line shows that well I guess good news for me it shows that by the end of the year I might end up with 1,000 Surplus okay let's go through data model there are just two tables dat table which is quite standard for pobi models and expenses in income that's is the table where I store my transactions let's see this is how the data looks like the important column for us is running burd down that's the one that I would like to predict and to see if I keep my current expense rate where I will end up at the end of the year will I go into minus or will I have something to spend if you would like to see how running burn down is calculated give me a second I will show you it's calculated using M code and give it a second to load let me open advaned Editor to show you all of the steps you can pause if you want to because I com commented all of the code so if you would like to read it after you can screenshot it or pause it for a second and go through it but basically what it does is it adds index column and then iterates through each column using that index here is my starting budget which is 20K and then it sums up all of the expenses that I have in amount column because those expenses have minus sign in front of it it's actually instead of summarizing it actually subtracts those expenses from 20,000 starting budget and it it rise through each row and in the end it removes index column because it's no longer necessary after the calculation and I have no use for it again you can pause and read through it if you would like more details it's not the topic of today's video let's go back to the report now that we know how run and burndown works let's see how to calculate linear regession actually it became way easier I believe last year when Microsoft added new function specifically for that to calculate linear regression before it was possible but with some work grounds now you can actually use inbuild function into Dax language the thing here is that this fun function use non-standard syntax basically you will not see uh code suggestions in case of this function because it's non standard and I will link in description to the article which goes into more detail how this function works but basically what happens instead of outputting the line it actually outputs the table and then from this table we we need to extract some values that we are using for calculation by default it won't work so let's let's walk through the Dux measure and see how it works so there is variable called line which is our line so remember from the explanation that I did here is our fun function that Microsoft implemented which calculates linear regression linest X it goes through all of the records and removes all of the filters from expenses and income table and goes through all of them our dependent variable as we discussed already is running burndown and independent variable or our time series is a date fied now the slope which is an angle of that line is one of the fields which is output of that line x linest x function therefore we need to extract it and that is part of non-standard syntax so we're selecting column from that variable which is called slope one as you can see it's under lined with red because by default that syntax shouldn't work but just in this case it does the same happens with intercept we extracting one of the um one of the fields intercept is our St St in line our X is our date as I mentioned already our independent variable or Time series and last variable is Y which is calculated this way and then we're returning y as a result of this Tex measure and let's see this is how easy easy it is to make linear regression in pobi one more thing I want to discuss with you is to actually show you how linear regression reacts to outliers I mentioned that point in the beginning of the video that linear regression actually not very good for outliers let's see how it works in practice so basically I will remove one of the filters that I have for my data because currently all of the data in this table is filtered up until today I have few records in the table that are in the future the the expenses that I know already or income that I know already will come on certain date so if I remove that field filter and let me copy this line somewhere because I would like to have it back once we finish here so I will copy whole M code and remove that latest filter so we have all of the records and load data back into the model let's wait a bit it's loaded and now let's see what happen to linear regression come on okay here is out outlier I will receive to give you some details I will receive some refund from from four utilities that I paid in 2023 and of course I marked it as a income but what happens is because it's an outlier and it might not happen ever again or might happen no one knows but because linear regression doesn't react well to outliers now we see that it shows probably that won't happen and that by the end of the year I will end up with 5,000 Surplus that's the result of outliers in your data if you have something similar you probably shouldn't rely on those results and I will not be relying on those results so maybe what should happen is that in my calculation I should filter out those outl buers that's all for today in the next video I will show you how to make app like buttons in porbi to make your reports more interactive subscribe and see you in the next [Music] one

Channel: The Introverted Manager Show

Views: 3,549

Keywords: Power BI, Linear Regression, Data Analysis, Predictive Analytics, Forecasting, Business Intelligence, Power BI Tutorial, Data Modeling, DAX Functions, Power BI for Managers, Budget Tracking, Data Visualization, Power BI Reports, Financial Forecasting, Management Strategy

Id: jZvwS8sc62s

Length: 12min 25sec (745 seconds)

Published: Sun Feb 11 2024