What is a Date Table / Calendar table in Power BI / Excel

if you're using power bi or power pivot to build a report by month by year then you need a date table end of story there's ways around it but don't avoid it it's easy to add a date table i'll show you how i'll show you why you should and i'll show you some of the little tricks that you need to know about let's go [Music] so if you don't have a date table and you've left a certain setting on and i'll show you that setting in a second then when you bring data into your power bi report or powerpivot you will have um a little date field in here with a little hierarchy so if i want to see sales um by i don't know let go to the date hierarchy by order or by due date year and month and i'll just drill down into here okay that's great however you've got the long month name what if i want a short month name you're stuck um what if i want to slice and dice by order date and due date in the same table you're stuck let's have a look what happens if you publish this report and you want to connect to it with analyze and excel so here's my connected excel report just bring this pivot table list over here you'll notice that the hierarchies have gone and if i want to do some analysis of sales by order date for example then there's no way of doing month and year you're stuck so the hierarchies are annoying they actually take up a lot of memory potentially in your power bi file because what happens is that for each date field you have in your data and here i have order due date and shipping date power bi automatically generates this invisible calendar with this hierarchy of year month and quarter in if your financial year is different to the calendar year you can't use these they're just not good okay they're there for simple quick reports when people don't know about calendar tables or date tables you really do need one and it's so simple so first things first let's turn off this auto date time hierarchy so you go to file options and settings options and then in the middle of your options screen you will see a little check box called auto date time turn that off and click ok and it will only impact new files notice i've still got the hierarchy in this old file so i'm going to do is start a brand new file re-import this data and show you what it looks like so here we are we've got our sales data reloaded this time i i've turned off that auto date time feature and those hierarchies are no longer there but then you're saying okay well i want to analyze sales by month what do i do i'm stuck this is where a calendar table comes in so i'm just going to go over to another report where i've built a calendar table in power query you can also build a calendar table using dax my preferred method is power query i find it more flexible i can copy and paste it into excel i can copy and paste it into other power bi reports it's a personal preference but a dax calendar can work as well so here we go i'm just going to open up an empty file with a pre-built calendar here's my calendar pre-built for me i've even got a couple of little folders in here with financial year and financial and relative periods so days ago months ago which can be really useful for when you're doing sort of moving totals or relative comparisons this month versus two months ago let's take a look and let's just copy it across to start with hook it up and then i'll explain how you edit this calendar so i'm going to go into power query and it's so simple you just right click copy this calendar you can then close this window you can even close this file down completely that clip that um calendar is in the clipboard in memory now so i'm into my report i say transform data i come into the same left hand panel here and paste okay and my calendar is now built easy the one thing i need to make sure is that my start date and end date cover the period of my data so in this scenario my start date is going to be the first of january 2001 okay because my data is actually back to 2001 and my end date so i've put in this little years in the future so if you want this to be ending one year in the future two years in the future if you're doing three year forecasting then you put whatever years in the future you want this to be then the end date is dynamic it's actually based on this little formula okay so this m code takes today's date and then runs it out to the end of this year if you've left it a zero for years in future if you put one year in the future it'll go to the end of next year if you've got a two for years in the future it'll run to the end of the year after so i've made this dynamic the today option i've just put in here which is again another formula another m code formula it's basically today that's the function for getting today's date i'll come back into here in one second but let's just close and load this and make sure it's all hooked up properly so once we load it we have our calendar table here we go and we just go to our data model and we make sure that we hook up our key field first so what i mean by that is the most common one you're going to report on so let's say order date is the most common one we're going to report on so i'm going to hook that up to my date but if i might be doing some analysis based on shipping date and due date i may as well connect those up at the same time okay remember only to bring in columns of data that you are actually going to use in your report if you're not going to use these columns in your report get rid of them okay right so by default the solid line is to order date so before you actually build any visuals click on the word month go to sort by and pick month number for sort that is a column where there's a one against january two against february and so on if you want to sort by financial year so you want maybe to start in july and run through to june then there's another little technique i'll show you in a second so month number for sort done same thing for day i want my day of the week you know if i just bring in day it'll actually end up being sorted alphabetically which isn't great okay so what i do is i click on the word day over here go to my sort by and there's day of week for sort this has got a one against monday two against tuesday and again i'll show you how you can tweak that if you don't want monday to be the first day of the week there we go monday tuesday wednesday let's just check that the months have worked as well drop them in here jan feb march and if you do want your months to sort by financial month number say july through to june if that's your financial year you can come to the month sort by and pick financial month number for sort and i can show you a quick trick about how you change that sort of what your year end is back in power query so let's just give that a go beautiful okay let me sort that back by month number for sort okay so if we come back in here now and we say right give me the sales by year by month and now i drill down i actually get a proper breakdown and notice if i actually go to here and turn the concatenated labels off it's actually the short month that i'm using rather than the long month description you've got full control because it's power query you can cut the columns to whatever length you want them to be and you can add your own quarters and you can add all sorts of functionality in here then i can just you can also just group these together in a little folder if you want to using the modeling view so under calendar maybe i want my financial years and quarters all in the little fy folder okay perfect right so let's jump back into power query and take a quick look at what you sort of tweak so under the transform data we're in the calendar so these things are the key at the top okay the today which is automatically generated we can potentially overwrite that but there's generally not a need the start date you just type it in years in the future you just type it in end date okay it's at some point in the future but if you want to hard code this in well let's just go and have a look at the advanced editor so here we go today here's the formula start date now what you can do is you could say right i don't want this anymore so you code out those two end date parts and you actually code this back in so this end date is now a hard-coded date and if i go back here scroll up the top go to end date you could just actually hard code in whatever end date you wanted for your calendar and then moving further down you've got month number for end of financial year so a six is a june and so on so if i go back here and just click on month number there's a six if i want my year end to be august i could type an eight everything else just gets generated by the code the first day of the week is day.monday you can change that to whatever you want and again the code does the rest for you essentially the first and most important point is this it takes your start date which is set up here the number from that turned the date into a number takes the number from the end date and the curly brackets just generates this list essentially i then just click this to table button that converts it to a table so this is our list of dates we name the column we change the type so it's a list of dates and then a lot of the next steps are just doing this add column date year okay and the month then take the first three letters of the month take the quarter you know the day of the week so it's basically adding all these steps then i've done some extra little calculations here to work out the days since today so days ago months ago and so on and they're really useful for making some of your dax formulas simpler later on and then i've added a little section here that calculates the financial year okay what's the financial month number that based on that input we put right at the top okay based on this number six it says what financial number is so in this case july becomes one august becomes two and the whole purpose for that is to sort your months so they display in the correct order so there we go there's a little bit more to it feel free to download this file and have a look through the advanced editor any questions just drop them in the comments but we're good we have got a proper calendar okay so let me just show you what happens when we then publish this we can slice and dice it in any way but it also then works in excel so back into my file and i've loaded the calendar into this other file that had before the one with the hierarchies and i've hooked it up okay the same way i did the other the other file and you'll notice that the hierarchies have now disappeared there's actually a hierarchy in my calendar now which is really annoying um [Music] one way to get rid of that and also a couple of other benefits of doing this is to mark your calendar as a date table so if i right click and mark as date table you pick your date column from your calendar notice this warning it says that anything that was associate associated with the built-in data tables sorry with the built-in date tables they'll break so if you are retrospectively adding this calendar back in you will need to recreate your fields that you've dropped into your visuals from your calendar now rather than the built-in hierarchies i'm going to go okay and again you can build your chart out by day of the week if you wanted to it is not sorted by day so you can see the descending order so i go to day sort by you can see day is at the top with the gray on it i come down and i say day of week for sort now nothing happens in this visual it's a bit annoying you need to make sure you sort your calendar before building any visuals otherwise there's two clicks you've now got to go sort by and you sort by day and then it always sorts it back to front so you now go sort ascending that does your monday tuesday wednesday okay another reason a really important reason for marking your calendar as a date table is if you don't actually have a date field connecting your date with your date and when i say date with your date date from your calendar with date from your sales table if you've got a date key like 202108.01 [Music] then for stuff like time intelligence functions and stuff like that you really need to mark it as a date table so that it uses it properly so you may as well just get in the habit of marking your calendar as a date table note i call mine calendar lots of people call theirs table date but when i'm writing dax formulas i just something about it i don't like writing date bracket date when i'm referring to that column having a column and a table with the same name i just don't like it so i call it calendar date whenever you are writing a dax formula though so if i go new measure and i write a function like total year to date which you can only do if you have a calendar table so if i say sales ytd and let me zoom in here with control and mouse wheel equals total ytd okay so i want my sales dollar and see it asks for dates you need a calendar table and if it's not a proper date column connected to a date column then this doesn't work unless you've marked it as a date table as well okay so you always put in your calendar date in here notice the little quotes around the word calendar that's because calendar is actually a function in dax as well it's actually a function called calendar so it's actually differentiating it from that and that would be it for your totally other date unless you have finish not on the 31st of december if you want your year to start for your calculation if you want your year to start from the first of july for example then you just put 3006 that's your year end okay for whatever reason you put your your end not your year start okay so i hope that that helps explain calendar tables and you see how easy it is to copy and paste and change the start and end date let's just see this in action in excel so here i am back in the excel file that is connected to the data set i republished the file i've refreshed this excel file and now here's the calendar showing up and i can easily now do i don't know month years in the rows and then maybe day of week in the column so day into my columns and you notice my days are sorted because i sorted them up in the data set in power bi so everything just works better with calendars so please use a calendar look i can even copy and paste a calendar into an excel power query so i can happily use it in excel or power bi let me show you that just to finish off so i'm simply in my file with a calendar in it i go transform data to open that power query right click copy okay then i just fire up excel i'm just going to go straight into the power query window data get data launch power query i can then right click paste this so this is excel power query and again i can now go close close and load two and i could load this straight into the data model beautiful so calendar tables built-in power query they're my recommendation i'll make the calendar available have a play around with it download it for free reuse it put it in a template file start off with that calendar every time you start a new project with it pre-sorted ready to go so i hope you find that useful please share this with people you might think might find it interesting subscribe if you want to stay up to date and i will catch you later [Music]

Channel: Access Analytic

Views: 33,713

Keywords: Date Table, Calendar, Power BI, Excel

Id: LfKm3ATibpE

Length: 20min 5sec (1205 seconds)

Published: Tue Aug 24 2021