How to get a DATE TABLE easily in Power BI

yo adam saxon with guyanacube and in this video we're gonna look at how you can easily get a calendar table in your data model and why you should care about this let's go [Music] if you're finding us for the first time be sure to hit that subscribe button to stay up to date with all the videos from both patrick and myself all right calendar table patrick and i talk about this all the time if you hang out in our live streams we've mentioned it once or twice or more this is something that you should pay attention to in your model something that can help you in the calculations that you make and the dax that you write to get those amazing visuals so let's look at the different ways for how you can get a calendar table and then i'll show you once we've got it why you should care about this enough all this talking you know we like to do it here in guy in a cube let's do what let's head over to my machine so we've got some data already in the model if we look at the internet sales table we can see we've got an order date and a ship date so let's go ahead and let's create a visual here awesome little bar chart i've already got a measure here so we'll go and we'll grab total sales and then we'll save how do we normally want to look at this well we might want to look at this over a month period and so we've got january through december and this is kind of normal one thing you'll notice here is this order date has this hierarchy with year quarter month and day this is something that's on by default inside of power bi desktop this is referred to as auto date time so for every date column in your model it's going to create this hierarchy this is just an effort to reduce the friction of creating and modeling your data right so getting up and going quickly so that you can create visuals and create meaning and get insights from your data however when we're working with larger models or we're working with standardized models and we want to get some consistency having something like this doesn't really work well let me show you so if i want to go off of ship date here then how does this actually slice if i want to compare the two how do i do that that's something that's difficult when we've got different hierarchies that are part of this and we don't have that central date table and so if i have a slicer and if i drag month we'll just change that to a drop down then i say march that works great so for the month of march i've got that much in sales across my given item but you'll see here that it's order date of month so what if i wanted to bring in ship date month and if we get rid of order date if i want to change this to march it looks a little weird right so we're filtering data in the date table but it's doing it off of the ship date but the order date has a different range so what are my options if i have a central date table then i can actually go through and use relationships to drive that particular slicing and dicing that i want to do and i can control which relationship to use from a dax perspective so how do we get that central calendar table there are a couple different options the first is going to be if it's in your source just bring it in a lot of organizations especially large organizations have a central calendar table that they use that aligns to their business so fiscal periods as well as calendar year periods and so if that's available for you take advantage of it i see a lot of organizations also use that central calendar table as a data flow as well that you can just pull into your model which is great so again if i just go back to my source and go look at the tables we'll see here that i've got dim date it's already got everything broken out by month a year a day what maybe holidays that are there that could be something that's already set up another option we have is that we can just go with dax and so we can go to modeling create a new table and then this will create my calendar table it's got all these things broken out and i can hear people already but what about power query yes you can do this in power query as well normally i'm saying do things as close to the source as possible the calendar table is one of those things where i actually like to use dax just makes a little more sense to me that's what i'm used to right and in this case i'm using the calendar function you can set a date range you can use calendar auto as well which will scan all of the dates in your model one thing you'll notice is that these hierarchies are still there so what i'm going to go do is i'm going to go to file and i'm going to go to options i'm going to turn off auto date time under data load and we'll undo auto date time you're going to see these automatic hierarchies just go away and then whoa that all looks weird because we got rid of our given hierarchy so now it's doing it by every day also from an auto day time perspective i did a video go check it out about how this could affect your data model size as well it's rare from my experience but it is something to be aware of so go check out that video if you want to learn more so we've got the auto day time capabilities we can bring it in from the source we could do power query we can also create stuff in dax but then i got to know how to write it intact there's got to be an easier way adam and there is now one of the things that the folks over at sql bi introduce is this new tool called bravo i'm pretty excited about it the thing i love the most about it is the ability to create a calendar table easily so go over to bravo.bi and you can download this tool it's a free tool that you can go install if you install it and you have admin rights it'll be an external tool inside of power bi desktop or you can just go to the start menu and go run it from there let's take a look at how this can actually create a calendar table for me i'm just going to delete this calendar table just know that if i did have a dax table already generated bravo can overwrite it it's fine just be careful about different field names that you have so that could become a problem so i'm going to go to external tools and i'm going to launch bravo it's that easy and there's a lot of different features in this tool but i'm gonna go right to manage dates because that's what we're looking in and it's gonna check your model to see if there's any potential issues so if you did bring it in from the source that might be something that could potentially cause a conflict so it'll flag that for you there's also different templates that you could potentially use and so you've got a standard monthly you could do a custom template or you could do like a weekly template if you're looking for different ranges like four four five four five four different types of calendars that might be used so i'm just gonna go with standard you can also set the first month of the year so this is in the context of your fiscal year so let's just say that the first month of the year is july and then your first day of the week for me i'm gonna go with sunday and you get a little preview down here about what this is actually gonna look like so the interval you can set it to auto which means it's just going to go look through your model and figure that out or you can hard code what your range you want to include in your model so here i'm going to do a automatic scan i'm going to say full or you can actually choose specific columns you want to go off of so like if i just wanted to do the order date and go off of the order date i can do that in this case i'm just going to do full from a date perspective you can choose your model language you can also change what you want the table name to be so instead of data i'm going to go with calendar you can call it whatever you want you can also include holidays or choose not to include holidays and also choose the country that they're coming from the other thing that's really cool is the time intelligence function so we can actually go through and try and create time intelligence measures based off of your existing measures that are there i've only got one measure so it's fine but you can also choose the measures that you want to do this on if you don't want to do it on all measures once that's done i'm going to say preview changes this is what it's going to create and then i'm going to go through and say apply changes we're done so now if we go back to our model here you'll see that we've got a bunch of different things i've got holidays and i've got a calendar table and then you'll also see the time intelligence folder here which is all the time intelligence items based off of my total sales amount that's bananas i didn't have to go and write all of that so let's fix this up let's go back to our visual i'm going to bring in calendar and so instead of order date i'm going to bring in month and we'll see here that it looks a little weird what we need to do is we need to create a relationship the other thing i'm going to do real quick is just change my slicer to go off of month on the calendar table as well so let's go create that relationship so we've got our date and i'm going to drag its order date and then we've also got date and i'm going to drag it to ship date it's going to create an inactive relationship on ship date and now we'll see that we've got data here we can see march you can see february everything's great and now you're saying like okay well what about that ship date so here on ship date i can create another measure that actually handles ship date itself so let's do this new measure so here i'm going to create this measure so shipped date sales and what i'm saying is take the total sales amount but use the relationship off of ship date instead of order date and then what i can do is just bring in both values and now we can compare the two and so now we've got our total sales next to our ship date sales and we can see the differences between those and when i slice by given month everything works because i have those relationships and it's driving off of my central calendar table a calendar table can help your model to be more effective and to give you more flexibility in terms of what you can do and what you can control from a central place and the bravo tool can actually be used to get you that date table very quickly without having to write dax all right i want to hand this over to you what do you think have you used bravo did you know about it let me know in the comments below if this has helped you and if you like that calendar function inside of bravo and if it made it easy for you if you like this video be sure to hit that big thumbs up button smash it if you so desire if it's your first time here hit that subscribe button and as always from both patrick and myself thank you so much for watching keep being awesome and we'll see you in the next video

Channel: Guy in a Cube

Views: 128,634

Keywords: bravo, bravo bi, business analytics, business intelligence, dax, introduction to power bi, learn power bi, power bi, power bi dashboard, power bi dataset, power bi demo, power bi desktop, power bi desktop calendar, power bi desktop date table, power bi desktop tutorial for beginners, power bi for beginners, power bi model, power bi pro, power bi training, power bi tutorial for beginners, power query, power query in power bi, sqlbi, sqlbi bravo

Id: WybnTHDl-AM

Length: 8min 48sec (528 seconds)

Published: Thu Apr 14 2022