Create it I'll share with you my favorite calendar table trick month offsets and that's gonna make your life so much easier we'll talk about fiscal year calendars you know the funny ones.
That end in June or October or a different month we'll have some fun creating a calendar table using a dynamic date range and who knows maybe we'll run into other fun calendar table.
Topics and cover those as well so maybe you're asking avi why do I need a calendar table I'm doing just fine without it my friend I don't think you know what you're missing I've been.
Building power bi models since 2009 and to date I have not built a model which did not have a calendar table my rule is simple if there's a date field in your model you shall create a.
That one for later now time intelligence is covered inside the learned power bi main course in the time intelligence lessons but let me recap here you must have a calendar table to be able to use.
Any time intelligence functions okay ah bi get it I'm gonna use time intelligence functions I know they're incredibly powerful so what do I need what this is what you're gonna need you.
Need a calendar table and it really has only two requirements it needs a date column and make sure that the data type is set to date and that column has to have contiguous states that means you.
Can't skip any dates but avi we don't you know we're not open on weekends skip those no you can well what about if we are closed in December for Christmas holidays nope you can't you gotta have a.
Contiguous date range so really the date column is all you need in the date table or the calendar table as far as barbie is concerned all of the other columns well they're needed for us humans and.
Posts Related:
- How To Create Your Own Tournament Generator With Leaderboard In Excel Free Download + Masterclass
- Acrobat Pro DC Tutorial
- Excel 2019 Advanced Tutorial
I'll talk more about that later
Got that are we but what date range do I need in my calendar table well the date range should fully clothed or cover the date on your tables so if you have a.Sales records from year 2014 to 2017 your calendar table should at least have that range it can be bigger that's okay too now it's time to reveal the second.
Reason for the calendar table it serves as a lookup table and perhaps the most important lookup table ever now we have talked about look-up tables and data tables at multiple spots inside the.
Course that's a good reason because it's one of the fundamental concepts in power bi but let's do a quick recap data tables represents a business process they capture information about the.
Process in this case sales now lookup tables are the who what where when how so if the sale occurred who bought it what did they buy when did they buy it where did they buy it and how did they.
Buy it so here the calendar table represents the crucial when but if you think about it it's not that different from other look-up tables let's compare a calendar table with the product table.
And product table has a unique key which connects it to the data table it's the product key calendar table has a key as well the dates column is its key now don't worry if your data is not at a.
Daily level let's say it's monthly or quarterly you can still connect it to your calendar table going back to product besides the key it has a rich set of attributes that let it slice.
Lettuce slice and dice the data we can look at CS by product giri's subcategory model name etc the same way calendar table also has a rich set of attributes that lets us slice and.
Dice the data we can look at it sales by
Ear month quarter week day etc so these are the two key reasons for having a calendar table ability to use time.Intelligence functions and it also serves as a very important lookup table so I hope you're never gonna go without a calendar table in your model and in the next video I'm gonna show you what's.
The best way to create a calendar table and it's super easy hey keep watching more videos and deep learning power bi but if you did enjoy this video I would love to hear from you so leave a comment.
