Calculate Difference This Year vs Last Year in Power BI with DAX

[Music] now to work out the difference between the Shan lash you've got to work through a few steps the very first step is to work out what our initial measure is so in this case what we're going to do is we're going to sum up the revenue column here so if we look down to the underlying fact table we've got a total revenue column which is great because now we all we have to do is create a simple measure on top that so what I'm going to do is I'm going to create a whole sales measure and I'm going to go sum total revenue total revenue column if we push enter you'll see that it's actually gone on our customers table so we need to actually change that so the way to change that if you are unsure is just to go to the home other modeling tab and then home table and then that gets it that gets it back into the sales table workwear which is where i want it now currently and so by drag total sales then you'll see them now they're actually we're actually getting a result here now this is the first step to doing any time intelligence you can write quite complex time intelligence functions but what I believe is the easiest way to do it is to actually go step by step build or build upon your simpler measures like we've done here to then go to your time intelligence calculations so almost like building blocks always build a good base and then the the more advanced things are easier to them from it's later on down the track so the next thing we have to do here is we need to work out we need to work out well first of all let's actually review this information but let's review it by date because we're going to start comparing by dates here just have to get rid of the hierarchy and then you'll see here that we have for every single day we have the total sales which is what we want so now we want to actually work out what were our total sales last year now this is an to show a result for that calculation only because this is the very first day so that if it goes and next looks back to last year doesn't it going to be nothing there so what we but anyway well we will go through the calculation and then we'll see how we can actually fix it so I'm going to create a new measure here I'm going to use them at unintelligent function and I'm going to go total sales in this case I'm just going to go ly for last year and we're to use a time Intelligence function inside of calculate now remember calculate changes the context of the calculation so what I'm going to do is I'm going to put in my expression or just total sales and then I'm going to go at the filter at hand this is where we're going to add in that time Intelligence function and power bi makes it so darn easy we're going to go as same period last year and then enter in a dates column from our dates table and then close that off and push enter now if I drag this measure on into our table now we'll get very different results and that's because we've we have written this time Intelligence function within calculate total sales is doing exactly the same job we're just giving it a different context to complete that job in so what we're going to find here is that this is the very first sale that we'll ever made for this analysis but if we go down to the first of the six 2015 you'll see this exact same number so let's now do that I'll show you what that looks like so we reach our first net at first our first duck date where there was a sale last year and it's the first of the six 2015 and everything is going to relate everything's going to every result here is just going to be the corresponding date one year before and then that's because off the calculation that we've done here now for our last step what we need to do is we need to work out all lots of difference between the past sales as shippers as a sales last year so we want to create another measure and say if between this year and last year now what and then we've already got our measures there so what I'm going to do is make a total sales minus total sales last year that's all we've got to do reference the measures we've already created and I'm going to push into and then if I drag that in here you're going to see that we get some interesting results first of all if we go down to where we actually have a sale last year you'll see here that this actually makes sense because because we've got a result here we've got a result here these results don't make any sense because all it's doing is going total sales minus of nothing and it's getting exactly the same result so we need to add some logic in here now it's quite simple we're going to do is if total sales last year is blank if is blank total sales last year you want to make sure that that equals blank and if not then we want to equal the results so we close this off you'll see now that there's no results until we get down to the first of June 2015 and we start getting the correct results so let's cool now is we have this calculation we've gone through a three step process there but we have this calculation who's going to copy and paste this here we have this calculation now that we can actually turn into your visualization just like anything else and we can we can filter this by any timeframe so we might want to actually look at a particular I'm just looking at these numbers here and I can already identify there's another issue there but that's as we go forward but if we go back to say 2016 you'll see now we can say see on a daily basis what the difference was between this year and last year and check this out say we wanted to look at this from a monthly perspective instead of a daily perspective it's as easy as copying and pasting that visual there and I'm going to grab my month and calendar in this case that's just a month and year and you will see that operators in the wrong ones so just change the back to year and then I want to change this to month and calendar 2016 and you'll see now that this is actually their monthly this is actually the monthly difference so it's aggregated it up but let's use exactly the same calculations which is makes things incredibly easy for us because if you think about it we could now use this these same calculations the same three-step process we could actually use it across any date dimension then is in our date table so extremely powerful way of discovering like very quickly or doing some time comparison very very quickly in your in your analysis

Channel: Enterprise DNA

Views: 233,102

Rating: 4.8865247 out of 5

Keywords: Enterprise DNA, Sam McKay, Power BI, DAX, PowerBI, Power BI Desktop, Powerpivot, DAX Power BI, Power BI Time Intelligence, Power BI DAX, How to learn DAX, Learn Power BI, Power BI training, DAX training, Time Intelligence, Data analysis, time comparison, measures, measure branching

Id: JNt-_QByeLk

Length: 7min 11sec (431 seconds)

Published: Fri May 12 2017