Working with Time Periods in Power BI

yo what's up it's patrick from guy in the cube and in this video we're going to talk about time periods i'm not talking about today or same period last year i'm talking about like yesterday or maybe last month how do you do that do you create a measure for each one what if you have 10 measures you create 20 measures or what do you do right so that's what i'm showing you in this video stay tuned [Music] if you find this for the very first time be sure to hit that subscribe button to stay up to date on all the great videos from both adam and myself okay so time periods you can have a yesterday you want to look at the last seven days you want to look at last month or last two months how do you calculate that how do you deal with that in your model so there's lots of different ways you can use relative date slicer possibly right you can do you can write measures to accommodate it there's it's probably not so flexible that it provides you with lots of options in this video i had a specific question about yesterday and last month and i'm going to show you a couple of ways of how you can solve this all right so instead of all this talking you guys know i like to do let's do what let's head over to my laptop so what you do is go to your calendar table and what i like to do when we're talking about you know last seven days or last one day or yesterday or something like that i like to just create a new column and in the new column the first thing i do is i create this thing called days days from today days equals and then i'll write something like this i'll use day diff and then i'll say you know start with calendar the calendar date and then go from today right and then up comma and then day right just like that so there's my days from and so we'll click check to make sure i didn't mess up anything and then we'll add a calculated column to our model and then you'll go over and you'll see if we click on our table okay there we go you'll see days from right if i sort this if we sort this descending you'll see you know that's today that's yesterday and on and so forth patrick you told us if we need to add a calculated column don't start in dax store it in the source do it in power query absolutely absolutely you can absolutely if you can do it in the source if you don't have access to the source do it in power query if you have if you don't do it in dax doing it in dax because it's just easy and if you guys get a copy of this file it's easier to work index than in power query okay so i do this and then how do i do yesterday so now i've added one column and i got to add another column all right so i'm going to add this new column and you can do this all in a single column but you'll see why i'm doing it in two columns in a bit right so then i can say yesterday equals in a simple if if right calendar days from today equals one then it's yesterday right otherwise it's not just like that i've just built this little filter and then all i need to do is go back to my report and drag this over i can add filters on page i can make it a slicer it'll behave the exact same way i'm going to drop it on filters on page and then if we choose yes it's only going to select the data from yesterday right august 27 and everything's filtered down just works it's just that simple but now they say patrick okay okay all right i see you i see you they said patrick what about the last seven days well that's easy that's easy give me more of a challenge to say okay what if i want to see last month i was like ah that's a good one that's a good one right so let me show you how to do last month right click on the calendar again choose new column and then i already wrote the code and so what i'm going to do i'm going to paste this in a new column and paste this right here and you can see all i'm doing is seeing if right the date diff between uh today and the day is equal to one month ago right if it's yes no right and so then i'll get rid of this let me make sure i check this and then i'll get rid of this slicer this one and then watch this i'm going to bring over uh last month onto filters for this page and then i'm going to choose yes and then watch what happens to this line graph right it's all of last month so this month that i'm recording the month that i'm recording this video is august and so last month is july and everything just sums up i know what you're thinking now patrick wait a minute wait a minute so i have to do create a column for each one yeah you got to create a column for each one that's not the end of this right i'm not lazy i'm just deficient i'm not going to take it through all those steps to create multiple columns multiple columns multiple columns let's get rid of that we're not doing that we're not doing that all right so let me show you a more efficient way my buddy phil c mark wrote a great blog post on this pattern using time periods to do this and it's really effective when you have lots and lots of data but you can use it for small data sets and you can also think about calculation groups and things like that um but sometimes you just want to use a filter all right if you go read i forget what the name of phil's blog post is but i'll post a link in the comments below so you guys can take a look at it um and it's a great blog post and it goes in depth i'm just gonna introduce you to the pattern and if you want more information about it go check out phil's blog post alright so check this out so i'm gonna delete this and what i'm gonna do is we can leave these here for now what i'm going to do is something kind of interesting i'm going to go home and i'm going to choose um i'm going to click modeling i'm going to choose new table and i've already kind of start writing this up okay because i don't want you guys to watch me tic tac tic-tac with dax too much but check this out let me show you so you start with this little sample code right here little sample code and in my sample code what i'm doing is you you need to choose a value that's like the maximum date of your value and anytime you refresh your report it'll keep going you know keep keep representing the maximum date in your value and then you kind of break it down by year month day these are just some some uh pieces parts of the date that you can use to make it easier to generate this table right uh so we're gonna do return do this this is a little constructor just so we can see the values that's being returned as a table right so i'm going to turn this off let's do this right so now if we do that what you'll see is if we go into the data view and we click on that table let's find that table so it's going to create a table for it's called time periods you'll see the single day right it's a single day if i change this to this month or this year whatever right it's going to show kind of more okay more eight this month i know what you're thinking again just like the calculated column can we do this in the source absolutely in fact on phil's blog he actually provides a t-sql sample where you can do this in t sql and gilbert q over at formu actually has a really good blog post and i'll provide both of them uh links below where he shows you how to do it in power query so if you can push it to the source or do it in power query absolutely do it okay i'm just kind of partial to dax and again if you get a copy of this pbix file you can work through it in dax all right okay back to my laptop so now you can see that now what i want to do is i want to return a table that represents yesterday right a single column table that i can filter my report by i have some code that i wrote and so it's a really simple little piece of code here and all i'm doing is like say if i got rid of my return that's all right there we go right so i return and i'm generating a selected uh generating the table and you see i'm doing this series it's just one row yesterday right and then i'm returning the values so here we go so if we do that give it a second and you'll see right yesterday is the name of the time period the value i'm returning is the date right and then i'm just returning uh the axis all right so simple so easy you can continue to add other time periods to this and that's actually what they both phil and gilbert talk about in their blog post and so i have a a complete sample that i'm going to paste in and show you what i'm talking about so if we do this you'll see that i'm sad satisfying than both of the the requirements for the report yesterday and last month and you'll see right here i'm generating a series and i'm using a little dax to calculate last month and i'm using little dax to calculate today and so we had this conversation about it was like well what do you mean last month are you talking about not august all of july are you talking about the last 30 days right that's two different things and so you can actually use the yesterday snip codes code block to do last 30 days you would just change it right 30 and nothing from you know your subtraction just change the math but in our case in the case of the requirements for the solution we were solving they wanted the last month so if i'm in august only show me july if i'm in july only show in june and they wanted to also filter by yesterday all right so you do this and we click the check box and the code is the table is created and you'll see there's yesterday because today is august 28 and then this is last month because i'm in the month of august as the dates increment in when whatever date you're using as the base date in the code so will the values of this it'll dynamically do it it'll dynamically do it with the column approach too and that's the reason i like this you don't have to change anything it just automatically handles it so then you need to make one slight change to the model so you can go here and that new table that's there right you got your new time periods table relate it to the calendar table or your date table so that date that's there relate it to your date table and we're going to break a rule that marco and the world says right this is one scenario where you definitely want to use bi-directional filtering because it can go back and forth and in some cases you'll have a meaning to one-to-many relationship between this table and that one and so in our case it's just a one to one um but that relation probably i automatically pick it up regardless it doesn't matter and so now we switch back and what we can do is we can use that period okay drag it to our report canvas make a slicer out of this watch how cool this is make a slicer out of this and if i choose yesterday watch if i choose yesterday it only filters down the yesterday if i choose last month it filters the data down only to the data last month what now there's lots of other things you can do with this you can introduce calculation groups with this and have this hybrid approach with the time periods and calculation groups you can add more time periods to this table just by writing a little dax or doing it back in the source and power query but the possibilities are limit are just boundless and if you're working with a large data bar data volume and if you read at the end of phil's blog post you'll see where he does some comparison between it between using the traditional date or doing it in a measure or using the time period tables is significantly faster all right what do you guys think have you used this reproach have you ever seen this pattern before i'd love to know let's you guys know i like to do let's continue the conversation where in the comments below it's your first time visiting the guy on thecube channel hit that subscribe button you like my video big thumbs up as always from adam and myself thanks for watching we'll see you in the next video

Channel: Guy in a Cube

Views: 46,557

Rating: 4.9332023 out of 5

Keywords: power bi, power bi dashboard, power bi desktop, power bi desktop dax, power bi desktop tutorial, power bi for beginners, power bi last month, power bi last month vs this month, power bi reports, power bi time intelligence, power bi time period comparison, power bi training, power bi tutorial, power bi tutorial for beginners, time intelligence in power bi desktop, dax

Id: 8Mvr_AIw2DU

Length: 11min 52sec (712 seconds)

Published: Wed Sep 16 2020