COMPARE TWO PERIODS NATIVELY using Power BI // Beginners Guide to Power BI in 2023

in this video I'm going to show you how you can visualize changes between two different periods in power bi without using any custom visuals at all we're going to try to build something like this where you're able to select a different months that you want to analyze adjust the month period that you want to compare against and even select the different categories if you wanted to or even see them as small multiples something like this 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 recover 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 before I start I just want to give a shout out to Gustav Dudek on LinkedIn because this idea came from one of his posts he posts a lot of really cool ideas of some of the visuals that you can do in power bi using out of the box visuals without using any custom visuals so if you want to learn more about or even see what is possible in power bi I'll leave a link to his LinkedIn page in the description box below so let's start from scratch so you understand exactly how you can visualize something like this in power bi so here I've prepared an empty report or rather a clean Report with just some of the basic tables that we want to visualize for today so I've already created a line chart here which just gives total number of sales on all of these different ones that we have currently in place is being calculated by this measure called sales which is simply just multiplying the unit price of all of those orders against the number of quantity we have a few other tables here as well so let me just go through them very quickly so we have a simple calendar table which just has the dates or current range 1986 to 1998 as long as the months in in those periods we have the categories of those products we have the order details which contains the unit price and quantity to calculate the sales you have the orders table which has the order dates this is what we link up to the calendar table as well as the products name so and how it links back into the category so I've already prepared the model at least the very base model but in fact you can work with your own model to kind of visualize this kind of technique yourself so just going back to this report view here there are a few things that we need to set up the first thing that we need to set up is the ability to choose the months that we want to select or to analyze and we can simply just do that with this month or rather we can't just do that because if you try to select a month from this current list it will filter out the table what we need to do is we need to create a disconnected table a disconnected calendar table so that we can make selections without filtering the table so it's a technique that I use quite a lot and there are lots of different use cases that you might want to use it and this is just one of them I did Cover disconnected tables in a different video so if you want to learn more about it go check out that one so what we're going to do is we're going to go to modeling we're going to create a new table I'm going to name this one month slicer and then we're just going to reference the calendar table so just to create a duplicate of that so now as you can see if we drag in the months from that change it into a slicer you don't want this slider setting so I'm just gonna make it a tile something like this so as you can see as I make selections it doesn't filter out the line the line chart which is exactly what we want you might just want to format this a little bit just to make it look slightly different so you can change the format here to be month and year here we go there we are the next thing that we need to set up is a numerical parameter this is what we will use then to find out which period we want the to compare against what we have selected so if this one slicer is the selected period how many months from this period do we want to analyze again so what we're going to do is we're going to go to modeling and then new parameter numeric range we're going to name this one previous period I'm gonna go minimum of one maximum of 20 it can be anything and then we'll just leave that incrementing to one and we'll leave this add slicer to this page right here so what it will do is it will just add a slicer for us right here and you can basically just add numbers which we can then use later in our Dax code so I'm just gonna adjust this a little bit because I don't really want this slider so slider off and then background off as well so I wanted to have that on the top right here great okay so now that we have the two inputs set up now now we need to start working on some Dax codes so let's start with the first one here it's gonna be a new measure in our calculations table so I'm going to name this one start and and this Dax is basically what is going to Define where that start and ending kind of vertical line is or the horizontal line the one that goes above these two periods so actually just before I start just to make things easier I'm going to bring in the months and the start and as well as the sales here in this table so you can visualize how it looks like on a table against how it looks like on the line charts because sometimes I find it easier to see the actual values than a visualized version of it so let's start by creating a few variables here I'm going to create this first one called top and this variable is what we will use to identify what is the highest point in the current context of our line charts and that's so that is to make sure that this horizontal line that we're going to create is always above the highest points in the charts just so that it's not conflicting with anything in your visual so we're gonna use max X and then for our table we're going to use all selected and then we will use the calendar month column and then the expression is the sales so that should be it the next thing that we want to get is the selected month actually just so that I can show you what that looks like I'm going to return it and just show you what that value looks like at the moment so 134 000 is the highest number here in our sales so that's what we wanted to get with that variable the next variable that we need is something called selected month so this is now to just find out which month is selected in our month slicer filter so this one simply going to use selected value and then we're going to use month slicer and again I'll just put it in the return here so you can see what this does so as you can see it's giving me December because that's what is selected here but as I change the selection so does the return of that variable okay let's continue so the next thing is the variable for what is selected for the previous month so this one is a little bit complicated so just bear with me here so the first thing that we're going to do is get the selected month so the current context and then get the previous month that is selected but make it negative like this and the end of month function that we wrapped it with gives me the current month minus the previous period value in months so what it does is if I show you here so it gives me now the 31st of this is August because that's one two three away from November but as I select October that adjusts and as you can see now as I adjust this one as well to maybe two periods so September it's giving me July instead so that's exactly what we want with this variable so now that we've set up all of our variables we now need to work on our if statements just to get the value from on just those months the previous month and the selected ones what we're going to do is we're going to create an if statement to say if the selected month is equals to the current month in context so I'm just going to wrap it in month or If the previous month is equals to the calendar month again in context but I'm adding an end of month and saying zero just to make sure because at the moment the month column is giving me the start of months whereas the previous month here I'm getting the end of months I need to make sure that when I'm comparing it against the calendar month I'm converting them into kind of the same range so they're all looking at the end of months so what I'm saying here is so if any of these are true then give me the highest point multiplied by 10 so this way we always have the highest value plus 10 on top of it so we're always having that space at the very top of our charts so let me just have a look here maybe I'm not paying attention too few arguments or pasts if selected oh yeah I missed yeah there we go so as you can see now on this table where it's giving us the value on the previous month in September as well as the current one that we've selected uh September and in the previous month the July which is the period here so as I change it the dates are where this is also changes okay it's a bit confusing because I'm sorting it by sales but if I sort it by months it makes a lot more sense so now let's start working on some visuals on our line chart here so the first thing that we'll need to do is add another y-axis here so instead of sales We'll add well on top of sales we'll add the start and end as you can see it adds the line and as we change the selection and the period so does the line chart changes let's work on adding some error bars as well this is the vertical line on top of these line charts so from here we're going to go to the line chart so click the line charts then just go to the error bars and then under sales let's add the start end and hit enable and there you go so you now have a line that goes above and vertically to the points in your line chart we might just edit this a little bit just to make it look a little bit more similar so first we're going to remove the Legends then on the lines we're going to adjust the start end line change that stroke to be similar to The Arrow bar make the color the same as well so make it black and on the error bar I noticed that there is a marker shape we'll just make that none and there you go that's pretty much done for you so that already works as it is so from here you might want to now get the variance between these two points because obviously now you're pointing them out you might want to then show it as a label or just show the variance of it so we're gonna have to use another Dax measure to calculate and get those points get those variants and find the percentage so we're gonna go back to the data view here I'm going to create a new measure and we're just going to recreate some of the steps that we've already created so I'm gonna name this one called variance again selected month is the selected value from our month slicer and then the previous month is again so the end of months or whatever the selected month is and then get the previous months but negative because we're getting negative values there and then the third variable is the previous month's cell so here we are trying to get whatever the previous month sales is based on what this value is here that we have selected so we're gonna use the calculate function this is the easiest way I've found it works it will use previous month sorry we can't use previous months so we'll use dates between and then we'll use the calendar table and then we'll feed it the date so dates between function needs three parameters the start date and the end dates so for the end date we know what the end date will be which is just the previous month but for the start dates we need to get the beginning of that previous month so what we're going to do is we're going to feed it another one so-called dates the year of this would be the year of the previous months the months of it would be the month of again the previous months and then the beginning is number one so that should give us the previous month's sales right and then the last second to last variable that we need have I missed something here maybe just another closing there so let me just double check this one because this is a new function for us a new measure so there is an error okay sorry it's I messed this one up so the first needs to be a date column so now it's giving me 55 000 which is definitely the value from April 1997 which is exactly what uh what we want here okay so let's move on to the next one so let's move on to the next variable here so this one is called the latest point so this is basically where we want the the label to be which is going to be the most recent value so the one that we have selected basically what we're gonna do here is we're gonna say if end of months selected is equals to end of month the current calendar months in context we'll just make it true otherwise oh here we are again not forgot closing parenthesis there else we'll just give it false then close the if statement so I've put this into a variable because we're gonna reuse it later and I don't want to write it again so just to show you what the latest Point does it just basically finds out which value or which month is the latest month so it just gives me true if that's the latest ones so now we work on the actual variance so this is a long road getting here but we are almost finished I promise so percentage variance so we're going to say if the latest point is true so we don't need to say if it's true or not we just say if it's latest point then do a divide so this is where we're doing the variance so it would be sales minus the previous month's sales and we'll wrap it around we'll divide that against the previous month sales so that should be the variance so let me just see that so it's giving me 26 percent that would be an increase since April so that should be okay for now so we need to do some formatting there just because 0.26 is is not a percentage but we're going to do some label formatting now because this is actually what we want to show as a variance on our on our error bars or on our comparison charts so what we're going to do is we're going to go to now the return and then we're gonna add again an if statement here just to test and check that we are on the latest point because we just want this to show up on the latest Point not on the previous month as well so inside that we're gonna add another if statement we're going to say if the percentage variance is greater than or equals to zero then we once to have some icons so to add icons you just press the windows dots and look for the icon that you want so I know already what I want which are these so the arrow is going up and down so I'm just going to choose them and then I'm going to add some space in between and then I'm going to add some formatting on my percentage variance so I just want to say 0.0 percentage and I think I think I need that one and that's it that's that's a look and see I'm just messed up something here I just noticed so this needs to be outside of this if statement I believe double check just so that even if it's above or below it's showing the percentage as well so that's showing us now the variance so it's going either up what the percentage is and the arrow changing depending if it's up or down indifference so now we want to add this into our line chart which should be pretty simple so now we're gonna go to the format settings we're going to go to the the error bars sorry not the error bars we're going to go to the data labels I'm going to disable the sales one and for the start and end make sure that's selected you will have or you should have a new Option under values called custom label if you enable that you can now set up as your labels so we're going to use the variance as our label here as you can see it's giving us the percentage as well as the up and down only at the latest one that we have selected here so as you make these selections that number also changes and the arrows also point up or down depending on what those changes are so now let's move on to the last thing I promise we're almost done which is adding the colors to these labels because you might want to show if that change is either good or bad which we typically denote using some colors green or red so what we're going to do is just simply change the color which we're going to use again using a measure so we're going to create a new measure in our calculations measure table create a new measure here we're going to call this one color and then we're gonna start with a switch statement I can spell switch expression will be true and then we're going to instead of going through all of the logic we're just going to check what the label contains so we know that we've set up the up and down arrow so we know if that's up it's positives it should be green if it's down arrow it's negative so what we're going to do is going to say contain string variance like this and then if it contains let's say an up Arrow then the color needs to be green and then we'll just copy that one so if it's a down arrow it needs to be red otherwise say blank like this so now we simply just go to the data label once more go back to the data label colors we'll click the for conditional formatting here and then under rules we'll change that to use the the color measure well actually I will just choose the field value and use the color measure if you hit OK you'll see that it's now changing the colors as well depending if it's a positive or negative change and that's pretty much it for this demo so as you can see now you're able to change the periods change the period you want to compare it against and if you wanted to we're gonna just show you quickly uh you can see it by category so you can add the category as a filter on the top or you can simply just add something like a small multiple so if you wanted to see them or side by side we can just add something like this where you can go category category name so as you can see it's giving us those changes in all of those categories that we have for all of our products useful right thanks for watching as usual give this video a like if you found it useful give it a dislike if you didn't so I need 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: 4,620

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, 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 2023, power bi line chart, power bi compare, power bi variance, power bi delta, power bi difference, power bi comparison

Id: qfEaXEaUsYU

Length: 21min 10sec (1270 seconds)

Published: Wed Sep 06 2023