How to compare SAME PERIOD LAST MONTH in Power BI using DAX // Beginners Guide to Power BI

in this video i'm going to show you the easiest way to compare and calculate same period last month in power bi we're going to go through it step by step together so you can follow along as well all of that and more so without further ado let's get started hi my name is fernan and welcome to the solutions abroad youtube channel where we cover tips tricks and best practices when working with power bi 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 this video was inspired by a scenario that i encountered today so i thought i'd maybe show you guys how i solved it so here i created a demo to keep things simple i created a table called sales and this table just has two columns dates and amounts which is pretty much sales that have been created or accumulated for each of those days and this data spans from the beginning of this year all the way to today which is the 18th of february now from this set of data i want to know a couple of things i want to know how much i earn per month i want to know how much i'm earning cumulatively every month and also how well my sales are doing this month compared to the same period last month now because today is the 18th of february as you can expect you can't just compare the sales from this month compared to the whole of january because the month is basically not over yet so instead you want to compare your month date sales for this month compared to the same month to date except for last month which is 18th of january it sounds a little bit complicated but trust me this one is actually very very simple so let's go to our report view here and let me just visualize this on a table for you so here's the total amount dates and the sales for each of those days we're just going to create a new measure here i'm going to name this one sales month to date we're going to use a calculate here so that we can add a filter context to this calculation we're going to do which is we just want to sum the amount value and we want to use something called dates mtd i'm going to feed this one with the date column and we're going to add it in this table here so you can see what it does so what you'll see now is that it gives you the cumulative sales for every month so you'll see um january 2021 18 and the second day is 43 that's because it's 18 plus 25 and it gives you cumulative sales throughout that month and when you get to february it resets i mean that's because of this filter that we've created here dates month to date and if you want to know how to use the other to date functions like year to date quarter to date i've actually created and covered it in a separate video in detail so check that out if you haven't yet now that we have the month to date sales for each of our months it's now simply just getting the month to date value for the same day last month and this part is also pretty easy so we're gonna create it in a separate measure just to keep it super duper simple i'm gonna create a new measure again and this time we're gonna make name this one sales uh same time same period last month something like this uh we're going to wrap this also with a calculate and we're going to reuse this measure that we've created sales mtd and in the filter context we want to use something called date add and it just this is what we're going to use to kind of travel back in time and look at the month previously for the current context so in this case we're going to say for the dates give me one month before and it might sound confusing but it actually does exactly that so if you drag this on the table so you'll see that now so if you look at this first of february 2022 you can see that the sales monthly date is 36 but the sales same period last month is 18 and that's because on the 1st of february we made 18 in sales for the same period so that allows you to kind of compare how you're doing how you did in this period in time compared to the same period last month which is the beginning of the month and i'm not sure if this table is giving you a good way to kind of compare and contrast so what we're going to do i'm going to create a line chart here so just to show you how it looks like in a chart to show you how the performance has changed compared to this month compared to last month uh so i will just filter the dates to just show give me this month and i'm gonna maybe change the color for the last month to gray and then this month is let's say dark so there you go so it's a lot easier to see now um how we've performed this month which is the blue line compared to the same period last month which obviously we started off it seems quite strong however last month started to perform better but then eventually we kind of ended up almost at the same place another thing that you can add to make it easier to visualize is to show the performance in percentages so for that we're going to create a measure sales same period last month in percentage and this time we're just going to create a divide so we want to divide the sales month dates to the sales same period last month and then minus that by zero change it to a percentage and we can add it into our table here to show you what is the variance between the same period last month to the current month to date so you'll see if we just focus on the february here and probably we can just exclude the previous month just to make this a little bit easier to read here we go so you'll see that at the beginning of the month of february we've actually started to make more sales or better performance compared to the same period last month which is january however you know as the month started to go we started to have you know a poor performance and you know started to recover until we hit these current period that we are on now which is 18 of february which gives us basically one percent and this metric basically says that compared to last month we are doing one percent better so these metrics that we're trying to use and demo in this video just gives you a bit of an understanding of how well you're doing this month compared to the same period last month this gives you a general overview of how your cells are doing even before your current month ends so to finish up this demo let's add the last few metrics that i mentioned at the very beginning of this video so we can just simply create some cards for those so let's say we want to show the sales month to date currently which we have it as 485 and i think we also wanted to do the same period last month as a card so this is wrong because it it tries to get the total sales period for the whole of last month because we don't have a raw context anymore so to fix that very quickly we can just add the date here as a filter context let's do a top end so to basically give me the latest date in our date here but this should now give me the sales for the same period last month which is 481 if we look back here let's let me just remove this to show you that it's correct here we go 30 sorry 18th of january is 481 sales month to date and we'll hit the same problem with the sales same period last month per percentage so we're just gonna copy and paste this and drag the uh card there so which gives us now our current performance is one percent and that's really it for this video i hope you now know how easy it is to calculate and compare same period last month in power bi 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: 31,939

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, how to power bi, power bi how to, power bi best practices, power bi tips and tricks, power bi standards, power bi patterns, power bi help, power bi tips, power bi same period last month, same period last month, power bi datesmtd, power bi mtd, power bi month to date

Id: Gw0t4TKkzE4

Length: 10min 22sec (622 seconds)

Published: Wed Mar 02 2022