AAA Excel’s Power · Transcript · · Updated
PP04: Power Pivot Tutorial - 3 ways to create CALENDAR TABLES, Why Calendar Table in a Data Model? – Transcript
Video by AAA Excel’s Power · Watch the original on YouTube · Licensed under CC BY 3.0
Summary
AI-generated from the transcript. Verify details against the source video.
- Calendar tables are needed inside data models to use time intelligence functions, bridge between two fact tables, and perform date classifications without increasing transaction table size.
- Calendar tables can be created using worksheet formulas with dynamic array functions, power query, or directly inside the powerpivot window.
- The calendar table date column establishes relationships with other tables, allowing users to create reports classified by year, quarter, and month.
Key moments
Use this video with Pro
Reading, copying and transcript downloads are free. Create saved AI results for this video with a signed-in Pro account ($4.99/month).
Transcript
Search ignores accents. All transcript text stays visible.
if you want to understand the importance of calendar tables in a later model and to learn different ways to create one please watch this video till the end in the previous video pp03 we had discussion around filter context measures and calculated columns dax iterator functions and internal and external filters
in this video we are going to discuss five topics first one the importance of calendar tables how to create a calendar table using the sheet function basically we are going to use the dynamic array functions then how to create one using the power query and then how automatically you can create one inside the powerpivot window itself and finally how to integrate your calendar table inside the data model
let's try to understand why we need a calendar tables inside our data models first one is to use the time intelligence functions they're very powerful set of functions inside dex called time intelligence and we're going to talk about them in the next video in order to use this brilliant functionality you need to have a calendar table inside your data model second in some cases you need to bridge
between two fact tables using a calendar table so you have two fact tables each and every one had its own calendar column in order to bring data from both tables you need a calendar table in order to bridge between both of them third you need to do a date classifications meaning that you need some information about weeks month years quarters and so on and so forth in order to do this you need to add more
columns to your fact tables and this is not practical at all this will increase the size of your table and will consume more resources from your database so the ultimate solution is to create a calendar table and that's exactly why we are saying that creating a calendar table will reduce your file size now we need to understand how calendar tables looks like first thing you have to have
a date column inside your calendar table and this date column should contains continuous dates you shouldn't have any missing dates inside your date column and also you have to have a unique value for each date and thus you will have a primary key inside your date table you need to have a separate column for each date unit if you want to have the classification for day of the week you
should have a column for the day of the week a column for the month a column for the year a column for the quarter and so on and so forth and finally the date column inside your calendar table will be used to establish the relationships between the other tables inside your data model so the conclusion here efficient model has a calendar table imagine that you
have a transaction table like this you have four columns product id date quantity and revenue and assume that you want to add classification for the date inside your transaction table so you need at least four columns like this a column for a weekday a column for month column for quarter and a column for year in other words you are going to double the size of this transaction table and
imagine that this table containing hundreds or thousands or even millions of rows this would be very expensive to resources and will reduce the performance of your model big time on the other hand imagine that you have the transaction table and you leave it as is you'll not add any columns to this transaction table but you are going to add a calendar table like this one and a current table is nothing but a lookup table and if you look at this calendar
table you have a unique value for each date and you have a column for all the classifications and you will have a one-to-many relation between this lookup table or the calendar table and the transaction table i have here three records containing 1st of jan 2017 and i can have only one record in the calendar table for the 1st of january 2017 same for 2nd of january 2017 and same for the
3rd of january meaning that if i have data for three years inside this table and imagine that you have two hundred thousand of rows inside your transaction tables you need only maximum of 365 times three or one thousand rows inside your calendar table in order to do all the classification and do all the reporting by week by month by quarter or
even by year inside your data model and this is how efficient data model works using the calendar table let's have a look on our example for today as you can see i have two tables on the left hand side i have a transaction table if you check on table design tab you will see that i give it a name like sales t this one is a transaction table containing more than
two hundred thousand lines and if you check the dates it's representing the sales transactions for three years 2017 18 and 19 and it comes in four columns product id date quantity and drive on the right hand side i have a lookup table which is basically the product table it contains only three columns first one for the product id and then the product name and finally
the name of the category and i give this table a name which is basically product i already put these two tables inside the data model and you can easily check inside the powerpivot window in the powerpivot window i have my two tables the sales t table and the product table and i have created a one-to-many relation between these two tables coming from the product table going to the sales t table from the lookup table to
the transaction table and i already created one measure called total revenue which is basically summing up the revenue column and i already used these two tables and relation between them to create a sales report here is the pivot table containing the sales report a very basic report i have the revenue classified by the category and i call this pivot table sales report the only
missing part here is to have a date classification in order to report this data by quarter by year by week or even by month i have only a date column inside the sales t so i can create classifications inside the transaction table the sales t table however as we mentioned this will be too expensive for the resources and not the efficient way to create the date classification inside
the data model so we are going to use the calendar table technique and as we mentioned we have three ways to create this calendar table i'm going to start with the worksheet formula actually i'm not going to create my first date table or calendar table inside the same workbook i'm going to use another workbook and i'll tell you why later in this video so let me go directly to the other worksheet and start to build my
calendar table i'm going to start by entering the start year and end year the start year in my model is 2017 and the end year is 2019 then i need to create a date the start date will be first of january 2017 and the end date will be 31st december 2019 so i'm going to use the date function equals date and then tab
the year input will come from the year comma then one for january comma and one fourth of january then closing the bracket and hitting enter i'll do the same for the end date i'm going to write equal date and then the year will be 2019 comma this time the end month will be december so i'm going to type 12 and
then comma 31st for the last day of the month and also the last day of the year and then hitting enter because i'm using the office 365 version of excel so i can use one of the dynamic array functions i'm using this time the sequence function and the sequence function is basically creating a sequence of numbers according to the specifications that you decide inside the function itself so let's start by
typing equals and then sequence seq and then tab the first requirement and the only mandatory requirement is to define the number of rows number of rows in this case will be the difference between the end date and the start date so i'm going to select the d5 containing the end date minus d4 containing the start date and then plus one and this will give me the exact count of days from the
start date to the end date as i mentioned this is the only mandatory requirement inside this function accordingly i'm going to close the bracket and hit enter and let's see what will happen i have a sequence of numbers from 1 up to 1095 and this is exactly the count of days or the number of days between the start date and the end date in order to change this to a list of dates i need to change my starting point instead of one
to have it with the serial number representing the day first of january 2017 in order to do so let me edit inside the formula comma i have number of columns the second requirement but in my case number of columns will be always one so i can skip this requirement i'm going to type another comma the start point will be the serial number representing 1st of january 2017 so i'm going to select
again the cell d4 and then hit enter and here you go i have my start point is 42 736 and this is representing the date first of january 2017 i can change the formatting for this column to a date i can select something like this type of date it's easy to read so you can see here the start date is the 1st of january 2017 and the last date will be 31st december 2019 so the function is
working perfectly now we can start to add the classifications in the different columns inside this table i'm going to start with the weekday in order to bring the weekday for each and every bit of this column i need to use a function like text so i'm going to write equals and then text and then i need to follow the screen tip first requirement is the value value will be the first date inside my list but in order to
point to the entire spilled array coming from the sequence function i'm going to use the hashbound if you need to have some more information about spilled array and the dynamic array function i'll put the link of my series about dynamic array functions on top of the screen right now after adding the value to the next function i'm going to write comma and then the code for the format text i'm going to put it inside double quote and the code for the weekday will be triple d so i'm going to write triple
d close the double quote and then close the bracket and hit enter and here you go you have the weekday sunday monday and so on and so forth now i need the month number a very basic function called month i'm going to type equals and then month then tab i'm going to select the first date again i'm going to use the hash bound to select the interest build array and then close the bracket and hit enter and here you go
you have one for january and 12 for december this is exactly what we want next one will be the month name again i'm going to use the text function equals t e x t and then tab the value again the first date in my list and then hashbrown to select the entire spelled array the code this time will be triple m for the short name of the month and then hitting enter you have jan for january dec for december it's working
perfectly now we need to calculate the quarters actually there is no ready function to calculate the quarters so i'm going to try a very simple trick equals then i'm going to select the month number then i'm going to divide by 3 and hit enter but before doing this let me also use the hashbound in order to point the spilled array the entire spilled array and then hit enter let's see what happened i have third or 0.3
for january if you go down you have 0.6 for february and then one for march and then a fraction of two or something between one and two one point three for april 1.64 and finally two for june and so on and so forth if i manage to round up these numbers to the nearest one i'll get the exact quarter number in order to do so i'm going to use a function called ceiling.math so i'm going to edit the
Show the full transcript (50 more passages)
first formula after the equals i'm going to write ceiling dot math and then the number will be the c9 hash divided by three then comma significance will be one and then close the bracket and hit enter let's see what happened i have one for january one for february and one for march and then starting april i have two it will continue to up to june and then three
for july the start of the third quarter and so on and so forth so this trick is working perfectly but i want to add q and dash before the number so i'm going to edit right after the equal i'm going to write double quote q dash and then double quote and i'm going to use the n percent to join it with the function and then hit enter here you go you have q dash one for quarter one q two four quarter two and so on and so
forth i think it is working perfectly last one will be the year i need the year number i'm going to use a very basic function called year equals year and then the serial number will be my date i'm going to use also the hashbound to point to the entire spelled array close the bracket and hit enter and here you go we have the 5d classifications in five different columns exactly as required let's add some conditional formatting to
this table i'm going to select the entire table and then i'm going to extend my selection till the end of the sheet i'm going to the home ribbon conditional formatting new rule i'm going to select the second option format only sales contains no blanks formats border i'm going to select outline and then okay okay one more time and here you go you have the conditional formatting applied
to the non-blank cells let's try to change the start date let me try 2016 and end date will be 2020 let's check our table the start date is first of january 2016 and the last date is 31st december 20 so my table is working perfectly let me go back to my original dates let me again select the entire table i'm going to extend my selection till the end of the sheet ctrl x to cut
let me go to this empty sheet and then i'm going to paste here now i have my table ready in a separate sheet and it is ready now to be connected via the power query into my original excel sheet containing the data model and why we put this calendar table inside a separate worksheet because if you are using the dynamic array and the spilled arrays you won't be able to change this table into an excel table so you need to connect to other workbook
using power query in order to take a range that is not named range or even an excel table inside the same workbook that's why i put it in a separate worksheet now i'm going to save this sheet and close and going back to the original workbook containing my data model back to the original worksheet in an empty worksheet like this one i'm going to data ribbon get data from file from workbook
here is my file import from the navigator let me select the calendar sheet i have my data preview here transform data this will trigger the power query editor on the right hand side i have the name of my query let me add something to this name like wsf stands for worksheet formula then i have my applied steps source navigation promote headers and change type all is generated
automatically no problem let's check the data types date column is date abc for the weekday same for the month name i have whole numbers for month number abc for quarter and whole number for year all is going very good i can just go to close and load from the poor data window let me select existing worksheet b2 and also i'm going to check add this data to the data model and click on ok
the query will be loading and you have your calendar table here and it is also loaded to the data model i can go to the powerpivot ribbon manage to open the powerpivot window let's check the diagram view i have my new table here the calendar underscore wsf and i have all the columns now we are done with the first way of how to create a calendar table i'm not going to join this to any other tables i'm just going to leave it
like this until we see the second method which is using the power query now we need to do exactly the same but using the power query as you can see i have a small table containing the start date and the end date of my calendar table if you check the table design the name of this table is date input i'm going to data ribbon from data ribbon i'm going to use the
shortcut from table range once i click on it it will trigger the power query editor on the right hand side i have the name of the query coming from name of the table date input let me change this to date table and then hit enter i have my applied steps first one is the source if you look at the formula bar it used the excel dot current workbook function in order to connect to the date input table then i have the automatically created
change type step which i don't need in this case so i'm going to get rid of it using the red x on the left hand side and i'm going to start from the source step the first step is to separate both the start date and end date from this query or from this step to have every and each one of them in a separate step and then i'm going to combine these two steps to have a list of all the dates between the start date and end date in order to separate the first date i'm
going to select the start date and then right click and drill down you can see that the entire query converted into a single cell containing the date 1st of january 2017. if you look at the formula bar you will see that the name source source is nothing but the first step and then between two carry brackets i have zero zero here is representing the first record in the table in the previous step so i have here the previous step
the first record was this one this row and because the power query is zero based so the position of the first record is zero that's why in the next step you will see that it used the zero to represent the first record in the table then i have between two square bracket i have the the word date date is nothing but the second column in the table that was in the previous step what happened here in the new step that i
added i have the previous table i have the position of the first record and then the position of the of the second row and that was the way the power query separated the value inside this query but what i need is not the exact text of this date i need the number representing this date i need the serial number representing the date 1st of january 2017. so i'm going to edit this step and before the source i'm going to write a
function called number dot from i'm going to open a bracket and i'm going to close it at the end and then hit enter and you can see that i have the number the serial number representing the first date or the start date i need to do exactly the same for the second step let me first rename this step right click and rename let me call it start date i'm going to select the entire formula right
click and copy then i'm going to use the fx in order to add a new custom step once i click on it it will prompt me again to the formula bar select and ctrl v to paste and now i'm going to change the position 0 to position 1 in order to select the end date and then hit enter and here you go you have the serial number representing the last date or the end date now i need also to rename this step right click and rename and let me
call it end date and then enter now i'm going to add a new step i'm going to use the fx again in order to add a custom step in this step i'm going to show you how to create a list a list of numbers inside power query i'm going to use the carry bracket and then i can type let's say that i need a list of one two three so i'm going to type one comma then two comma then three can hit enter you will see
that i have list of one two three but let's assume that this list need to be from one to one hundred and not be able to write one two three four five up to 100 this is not practical at all i can just edit this one and write something like one and then double dots and let's say 100 and hit enter and you'll see that i have a list of numbers from 1 to 100 no problem at all now think about the start date and end date i have a serial number for the
start date and also a serial number for the end date if i can replace the one with the serial number for the start date and the 100 with the serial number of the end date i can create a list of all the dates between the start date and end of the date so i'm going to select this one and replace it with start date the name of the step and then i'm going to select the 100 and replace it with end date the name of the step for the end date
and then you have list of all the serial numbers representing dates from the start date up to the end date now i can rename this step to list of dates and then hit enter if you look at the result that we have now it is a list of numbers it's not a table but i can easily convert it to a table from the transform ribbon on the left hand side to table and then click you will see that i have a table of one column containing all the
serial numbers representing dates from the start date to the end date column one can be changed to something like date and then enter if you change also data type to date no problem you will have the date starting first of january and if you scroll down you'll see that last date will be 31st december 2019 now i can create the classification columns no problem while selecting this column i can go to add column
on the right hand side from date and time i can just select date and then i need to start with the week day so i can go to the day name of day and here you go you have the full name of the day if you need the short name no problem just select the column and go to transform very good options called extract i can just select first characters i need only three letters i'm going to write three
and then click on ok here you go you have the short name for the days second i need to add the month number i'm going to select again the date column add column date and then month and then month here you go you have the month number for the entire table i need also to put the month name again i'm going to select the date add column date month and name of the month same as we did in the day name i can
just extract the first three letters going to transform extract first letters again three and then okay no problem at all now we need the quarters i'm going to select the date add column date here i have the quarter automatically quarter and then quarter of the year i have the quarters as numbers if you need to add something like q and dash it's easy also i can
just go to add column custom column and then let me add something like q and dash and then okay here you go you have a new column called custom with q and dash i'm going to select this column and then the quarter from transform ribbon i'm going to select merge columns no separator required and i'm going to give a name like quarter and then okay here you go you have a new column
contains the queue dash and then the number of the quarter i think i have all the classification now except the year going back to add column let me select the dates column again date and then year and then year no problem at all you have the year number i think we have all the classification required we can just load this query going back to home close and load close and load two
let me select existing worksheet something like c8 and then add this data to data model and click on ok exactly as happened in the previous one i have the 195 rows loaded i have my calendar here let's go to powerpivot manage in order to check our new table diagram view i have my new data table loaded here i'm not going to integrate this with my original tables because
there's another way to do the calendar table we are going to see it in the next section the last way to create a calendar table is using the powerpivot window itself so from design tab i'm going to the right hand side calendar section i have an icon called datetable if you select you'll see a button called new just click on it it will
automatically create your calendar table it will start with a date column if you check the date column you will see that the first date is 1st of january 2017. if you go down to the end of the table you will see the last date is 31st december 2019 and this is exactly the beginning and the end of the sales t table so the way the calendar table created powerpivot window
is checking your model it will take the first year that appears inside any of your tables and it will start the date table with the first day of this year and also it will check the last year appeared in any date inside your data model and the last date of the calendar table will be the 31st december of that year that's why i have my beginning is
beginning of january 2017 and the end date is 31st december 2019 and also you can notice six date classifications automatically created first one is the year then month number and then month name and then month and year together number of the day in the week and the name of the day week and the only one missing that i need to create myself
will be the quarter number so let's check how this created if you check any cell inside this year column you will see that it's created using a function at x function called year pretty much similar to the one that we used inside the worksheet formula same for the month number same for the month date it's created using a function called format exactly the same like the text number it has two
parameters first one is the value or date value and then the code of the formatting and in this case you have four m's and this is the code for the full name of the month you can just delete one and hit enter and you will see that the month name now is only three letters this one is created also using the format and this column created using a function that also similar to the one that we have in the excel sheet formula
called weekday and the final one also using format and the code is for these i can just delete one and hit enter and i have the day name same as i did inside the other tables and now i need to add the one for the quarter i can just add another column i click on the add column i'll give it a name like quarter and hit enter it will prompt me to the formula bar i'm going to write a simple
dax formula i'm going to use the same function ceiling exactly like the one that we have in excel i'm going to select ceiling the number will be the month number divided by three so i'm going to use the month number column open square bracket and then select month number column divide by three and then comma significance will be one close the bracket and hit enter you will
see the quarters is from one to four inside the filter here and then i can also add the double quote q dash double quote and then the end percent also to join it with quarter number and hit enter and here you go you have q1 and if you open the filter you will have q1 up to q4 now my automatically created calendar table is ready i can just check it inside the
diagram view here you go the new calendar table is ready for you so i have now three calendar tables each and every one created using a different method i need only one calendar table inside this model so i'm going to select one of them and i'm going to integrate with the other tables inside this model
the very last step is to integrate one of these calendar tables with the other tables inside the data model for me i'm going to select the automatically created calendar table i'm going to select the date column inside the calendar table and i'm going to create a one-to-many relation with the sales transaction table so i'm going to select here and then drag it and create a relation directly and you
can notice it is a one-to-many relation the one side coming from the calendar table which is our lookup table and the many side inside the transaction table if you look at your model you will notice that you have many columns that you are not going to use inside your report so i'm going to hide the columns that i'm not going to use so let's start from the product table i have the product id product name and category i don't need
the product id right click and hide from client tool for the sales t table no need for the product id also no need for the date because now i'm going to select the classifications from the calendar table the quantity also is not required revenue is not required because i have the revenue measure i'm going to select all of this right click and hide from client tool for the calendar table i'm
not going to use the month number right click and hide also because this calendar table is automatically created and it is marked as calendar table you will see that there is a date hierarchy automatically created here so you can use that hierarchy or you can use any individual column from here also no need for the day of the week number i can just right click and hide and now i think i'm ready to create
my report so i'm going back to the excel sheet from the report tab i'm going to select the pivot table on the right hand side i have the pivot table fields from the old tab inside the pivot table fields i can just select the calendar table i can select more fields i can drag and drop month in the rows category back to columns and then i can just take the year on top of the month
and finally i can take the quarters on top of the month as well if you look at your report now you have a very very good report you have all the categories here you have the years you have the quarters and then you have the month i can just add some subtotals i'm going to design and then subtotals show all subtotal at top of the group so we have all the subtotals here but let's look at the
report itself quarter one feb jen and marsh it is not sorted also april june may it is not sorted august july september also not sorted all of the month field is sorted alphabetically and this is not exactly what i want so i'm going back to the data model back to data view from my calendar table i'm going to select the month column and from the
home ribbon you will see sort and filter i have a button called sort by column so i'm going to use sort by column i need to sort the column month by the column month number i'm going to hit ok going back to my report and you'll see it's automatically sorted in feb march april may june and so on and so forth and now you have your final
report coming from data model using three tables one transaction table one dimension table for the product name and category and one calendar table a final piece of info for this video if you want to change the range of the date that you have inside the automatically created calendar from the powerpivot window you can just go to the design ribbon and then date table and then you have the bottom update range if you just click on
it you can just change the start and the end date according to the new information coming to your data model in this case i'm not going to change anything just cancel close this one and save my report now i have my report final and everything is going according to the plan hundred percent that was a very long video we saw how to create a calendar table using three different ways and then we integrate the
calendar table inside our data model if you like this video please like it leave me a comment and subscribe the channel hope that was useful for you and see you next video and bye