How to Compare Two Time Periods or Dates Dynamically in Power BI (P1: Years)

[Music] hello everyone welcome to bi land today we are going to see how we can have kpis that compare to selected periods if you haven't watched our previous video on power bi guide for the beginners make sure to watch it also okay let's get right into it [Music] this is our model fact table dimension table and the calendar we have also a measure table which contains the measures we have in our back table we have oil and gas production consumption and also reserves in our dimension table we have the characteristics of the country like region sub-region etc and in our calendar we have dates our goal is to have two slicers year and relative year when the user selects a year and their relative year all the visuals and kpis do the comparison according to these selected periods let's see how we can do it one way to do this is to create a dimension table with your column and have the actual relationship with the fact table and also create this second dimension table with the same year column but this time have a passive relationship with the fact table so it is also the opportunity to see how the passive relationships work in power bi okay let's do it to create my dimension table from this column i can use values i call this them your values the function values gives me the unique values of the column so we use it sometimes for creating a new table here we are going to use our your column and now i have the unique value of the year column from the back table now i'm going to use exactly the same for the second table second table is name relative year values so i use again values function your column basically these two tables are the same what is going to be different is the relationship now i have my two tables i'm going to make a relationship with this is going to be one too many and my second relationship is going to be with relative here this relationship i want to make it passive what is passive relationship so if i uncheck this make this relationship active now this relationship is passive we make relationship passive so that we use this relationship only when we call it in a measure and in a measure there is a function to use this to make this relationship active which is called use relationship we see that it's a line with dots and different from the active relationship this relationship remains passive until i call it in my function okay let's go to our report page now that we have these two tables we can create our slicers first slicer is going to be the relative year i bring the relative year here i want it drop down and the second one is going to be here and also i want a drop down now the user can choose a year like 2014 and another year like 2020 and we are able to compare our numbers or our graphs in two different years one thing that is important here is to change the selection control to single select because i want to only choose one year here we compare 2002 data to 2014 and i'm going to [Music] and i'm going to change a little bit how it looks first items i want darker i want them larger header i want red larger and i'm going to make it bold and i can use format painter to have the same here good we have now our two slicers we should also change this to relative year to be more clear okay let's jump into the calculations we want a table giving us the oil production percentage change per region if i bring a table here and i want region for example for europe the percentage change of oil production compared to 2014. i have already a measure called oil production if i bring oil production to this table it's going to show me the oil production of 2020 because the relative year is passive relationship it means that this is not impacting this table or any other visuals if i'm not calling this relative year because in oil production it's a simple sum i'm not using this relative here if i change it to 2015 or any other year there is no impact on it if i want to have the second measure to see the impact of this one i can use the relationship if i change this measure to calculate this expression but this time in my filter i'm going to use this function which is use relationship use relationship wants me to put two column names these are the columns that the relationship is based on first column is the column in the dimension relative here this one and the second one is in the fact table here now this calculation calculates the oil production but it uses the filter in the report from the relative year so it's going to be 2015. but there's a problem here so if i hit enter it's going to be empty in my table the reason is that i have a filter on 2020 and this year also here doesn't say that we shouldn't consider the other filter coming from the other dimension to do that we can use all function so the second filter for us here is going to be all all basically clears filter coming from another column or table you can put here a table to clear out all of the filters coming from that table or only a column from that table for now we want to clear out the number coming from the other table dimension your table when i hit enter i'm going to see the the oil production for 2015. okay now that we saw what this calculation is doing we are going to go back to keep oil production only some of the oil production for the year dimension here as we saw this is calculating oil production for relative year but i want to use this in another calculation so i'm going to copy this and make this calculation as it was before so now it's oil production according to this year 2020. we want percentage change of the oil production compared to 2015 how we can do it so we are going to define variables our first variable is production which is the oil production that we have and our second variable is relative production which is going to be what we constructed a little bit ago i had copied it i pasted here this is calculation of the oil production which uses relative year dimension and clearing this dimension and i can now say return my return is going to be production divided by relative production as we have already seen we can only use the variables defined in this formula only here and not elsewhere and when i start typing the name of the variable i see that it proposes me the name of the variable with x y next to it i hit tab and then minus one enter now i bring person to change here i change it to percentage we see that compared to 2015 we have three and a half percent decrease in oil production we can change to the other years for example if i choose 1971 so compared to 2020 i have 75 74 percent increase we can change here also if i choose 2019 this is the old production for 2019 and compared to 1971 i have increase of 87 therefore we have reached our goal our goal was to choose any two years and compare the oil production dynamically we can improve this table by adding conditional formatting and colors on this column one way to do that is simply by coming here and conditional formatting background color we can change here to rules and then we can define rules like is greater or equal to zero percent or number or less than this then this color and then we can add new rules but what i prefer is to have this one based on specific measure let's see how we can do that so instead of using simply here the conditional formatting i'm going to create a new measure i'm going to call it percentage change color and here i'm going to define a rule which is if the percentage change is greater than 0 then 1 else if the person to change is less than less than zero then minus one otherwise if it's zero it's going to be blank so here if the percentage change is greater than zero it's one and if smaller than zero it's minus one else which is percent the change is equal to zero it's going to be blank so if i bring this color here i see it's one but if i change the year to 2015 i'm going to have -1 so now instead of using this color measure here i'm going to use the color measure here i have person to change i'm going to use background color i'm going to use rules and based on not the percentage change but percentage change color and i'm going to say it is one number it is going to be like it is going to be like green and if is minus one number is going to be red and this is too dark i'm going to change it like something like this okay i can further improve this if we need to by just coming here i prefer to have create another color here we can change the grid color i want this and i want to have column headers a little bit bold and also i want to change oil production i want to change i want to take out that and also i'm going to have field formatting basically in field formatting we can choose oil production and i want to be blind centered write total and i want to also do the same for person to change alignment center apply to header and total so this is my table and which is dynamic we can use according to the years chosen maybe increase the size values i want 12 i can create a little bit and column headers i want also 12. we save our document okay that's it for this video we saw how to dynamically calculate percentage change for the selected years we saw how to use the relationships passive and directive we used use relationship in our measures also we used all to clear out the filters and we used measure to do conditional formatting [Music] make sure to subscribe to our channel so not to miss the coming videos and tips on power bi until then take care see you soon bye bye you

Channel: BI Land

Views: 22,749

Keywords: MSBI, Business Intelligence, Power BI, DAX, SQL, SSAS, PAginated Reports, Tabular Cubes, compare two time periods, dates in power bi, time intelligence

Id: knXFVf2ipro

Length: 19min 26sec (1166 seconds)

Published: Thu Dec 23 2021