Calculate Month on Month, Day on Day, Year on Year using DAX // Beginners Guide to Power BI in 2021

in this video i want to show you how you can get month on month change in your data using power bi i'm going to show you how to get the actual value as well as the percentage change as well as the different variations of the month and months such as day one day or year on year all of that and more so without further ado let's get started hi my name is welcome to the solutions abroad youtube channel where we cover tips tricks and best practices when working with power bi i upload new videos every week so make sure you hit that subscribe button and the bell icon to get notified when a new one is out so quick intro on what i mean by month on month so let's say we are in the current month and in this month he sold an amount totaling over 1 300 pounds whereas previous month you sold about a thousand pounds worth of goods so a month and month would show a 300 pounds increase to your sales versus the previous month or a 30 increase so this type of change either the percentage or the actual amount is what we're after when we're using the month and month metric so now i want to show you how to implement the month and month using an example that i prepared for you today so here we are we have a very simple power bi report that just has one table the sales table here so if we go to the data view here you'll see that we only have a couple of columns that we can work with so it will have the products sold which is in the sales table so the type of products that were sold how many and what was the price and also when we put it in a graph here to show the total sales by year and month now to visualize the year month on both the line chart and in the table we create a relationship between our sales and we use the calendar table to visualize it and don't focus too much on the calendar table it's just a standard calendar table that we use as a central time intelligence calendar table for our data model here if you don't know how to create one i covered it in a separate video so check those out if you haven't yet and lastly what we have is the total sales measure which is calculating the total sales from the sales table now it calculates it by if we go to the calendar or the sales table here it calculates the total sales by multiplying the quantity against the unit price and that is what we are visualizing here on both of these two visuals if you look at the measures table here we have the total sales and as you can see here we have a sum x which just does exactly what i said so it's unit price multiplied by quantity so the first thing that we'll need to do is we'll try to fetch the previous month's total sales into the line of the current month that we're looking at now the dax function that we're gonna use here is something called the previous month and i covered it in a separate video but if you're feeling lazy you can just follow what i do here so we're gonna create a new measure here and we're going to name this one already month on month equals we're going to use a calculate so that we can add that filter function and in our expression we're gonna just use the total cells that we've created so this is the one that calculates by multiplying the unit price by quantity so instead what we want is we want the total sales for the previous month so we're gonna give it the calendar dates so if we hit enter and now we have this measure let's add it in our table here just so that we can visualize how it looks like on a table view so here we go so you can see that on let's say on the february 2020 we have the month and month one two four two point four three which is the total sales for the previous month so now that's the first step done so we're able to get the previous month's value by using the previous month function and this is what we'll need to use to compare against the total sales value but before we do that i just want to add just one thing you can see at the bottom here um it's created a new value here on the october where there are no total sales there and that's because there is a total sales for the previous month which is september which is why it's showing us october so we wanna make sure that we only show the months a month or we only do the calculations when there is a total sales for the month that we're looking at so uh in that case we need to put an if statement to say okay if you don't have any total sales for that month exclude uh don't show me any monthly month so to do that we can go back to our month and month here i'm going to just wrap this in an if statement so i'm gonna say so if is blank and i will just use the total cells here and i'm gonna say if it's not blank give me the calculate so this is what we're doing so don't get too confused because it seems to be getting a little bit ugly here but all it's doing is if it's checking if the total sales is blank or not blank and if it is not blank then give me the month and month otherwise exclude me so if i hit enter you'll see that that excludes the october from our table here now from here it's pretty simple all we have to do is do the total sales minus the previous which will give us the value change from the previous month to the current month so let's say for example from the february 2020 you can see there's a 57 pounds difference because that is the difference between the previous month sales and the current month sales however one thing i want to do here is i just want to make this a little bit readable so what i'm going to do is i'm going to wrap this if statement that gives us the previous sales into a variable and if you don't know how to use a variable it's pretty simple you first just need to declare it and you need to add the um the value that you want to add either a scalar or tabular so in this case we're adding just the value of the previous value and i'm going to wrap it in this variable called previous now that we've wrapped it in a variable we can reuse that as many times as we want in the code so now that we have created a variable called previous this will signify the previous month sales all we need to do is say total sales minus the previous month sales and you'll see it gives you the exact same uh results except that the return value which is the calculation that we are looking at is a lot more simpler to read now that you know how to get the month on month value the actual change between the uh previous month sales and the current month sales now we want to convert this into a percentage change which will show you how much your sales have changed in a percentage value now for this we need to divide the current um calculation that we have to the previous value and then multiply that by 100 this is the calculation in order to get the percentage change from one value to another so what we're going to do at this point is we're going to use divide which is the safer way to divide values together and what we want to do is we want to wrap this into its own entity so this will be the numerator and then the denominator would be the previous value so if we hit that and enter now we need to multiply it by a hundred in order to get the percentage value but in power bi you don't need to do that as long as you change the uh the view to be a column uh to be a percentage now that we change that into percentage have a look at the table so here we go so if you look at the table here now we get the percentage value change month on month for our data so pretty simple right so one interesting thing about our dax code you'll notice that we reused the previous sales variable two times in our return and basically it allowed us to reuse the same calculation without having to rewrite the whole thing which is one of the reasons why you should be using variables wherever you can so let's say you're not interested in month and month and you want to use different variations so let's say you want to see the year on year change on your data instead so how do you do that well the good news is that you've already done the hard part now to get the different variations like uh quarter turn quarter day on day year and year you can use the same solution and just tweak it slightly to make it work so let's go back to this uh dax code that we've created here i'm just going to copy it and we're going to create a new measure so i'm going to paste it here and instead of month and month we're going to name this year on year and the only thing that you need to change in this code is this part which is instead of previous month we want to change it to previous year and voila so now you have year on year measure created quite easily using the same solution that we've created for month and month so let's have a look at how that looks like so i'm gonna do uh let's say calendar for a year i'm gonna do year on year change and then we're gonna do total sales if we just change this to a percentage format you'll see that we've had a 20 decrease from our sales last year compared to this year so let's try it one last time with the day on day which again is the same method so we'll create a new measure once more i will paste the dax code for month and month here we'll change it to day on day and instead of previous months we'll replace this with previous day we've hit enter and that's it so you now have a measure a day on day measure that you can use to see the difference on a day-to-day basis so let's have a look and see how that looks like um in action so we're just gonna get the dates here we'll do total sales for those days and we'll do a day on day change we'll change that into a percentage value and there you go so it shows you the day on day change for your total sales on a daily basis so you'll notice that some of them are blank but that's because we have some dates that are missing here so for example you can see that we don't have a value on the third that's because there is no sales data for the second which is why it's not being shown there but any day that has a previous day is where it compares the data from and then checks the difference and that's really it for this video i hope it helped you understand how easy it is to start implementing something like a month and month change calculation in your power bi reports thanks for watching as usual give this video like if you found it useful give it a dislike if you didn't so i know to do better for next time ask your questions in the comment section box below so i can help you and you can help others if you really like this video we have a patreon page where you can support the channel and get exclusive perks like early access demo files and credits at the end of these videos thanks again for watching and see you in the next one bye

Channel: Solutions Abroad

Views: 94

Rating: 5 out of 5

Keywords: solutions abroad, power bi, powerbi, power bi tutorials, power bi for beginners, beginners guide to power bi, data analytics, dax, data modelling, data visualisation, business intelligence, power bi 2021, power bi month on month, power bi day on day, power bi year on year, power bi quarter on quarter, how to power bi, power bi how to, power bi guide, power bi automations, power bi percent change, power bi overtime, power bi previousmonth, previousday, previousyear

Id: cAxoxvnN_vQ

Length: 11min 46sec (706 seconds)

Published: Mon Oct 04 2021