To use it and also good counting the table is chock full of goodies and I know you're gonna love it it has all the usual calendar fields you would need and it's easy to customize if.
You need to so here are your steps there are many ways to create a calendar table using Excel sequel Asscher or dax but my favorite method to create is completely inside the power bi query editor and.
This is what you need to do to use the one I have created just for you you're going to download the ultimate calendar B bi X file link is in the details and then copy paste it in your model that's.
It well okay there are two more steps you would need to change some settings and customize it if you need but it's gonna be super easy let me walk you through the whole thing so ladies and.
Is open both of them and on the left I'm going to open up the query editor there we go I'm gonna move to the left and over here I'm gonna open the query editor as well and this is the ultimate.
Calendar table I'm gonna copy it from here right click copy and I'm gonna right click and paste it here and that's it you can close this you don't need this anymore.
And all you have to do is close and apply and lo and behold your model now has a calendar table so earlier if you hadn't of course this is a very simple model product inventory you have the new.
Table ready to go but before we can use it we have to follow certain steps let's go through that first thing we're going to do is we're gonna create any relationships that are needed so now if.
You go back to our model we have the calendars table so any table which has a date field which you would like to be connected to this lookup table you're gonna just drag and drop.
Posts Related:
- Python Part 14a - Working with numbers Office
- Learn How To Reconcile Accounts In Excel From Scratch + FREE Application Download
- Cara Mudah dan Cepat Bikin Pas Foto Tanpa Install Aplikasi
And connect that relationship that's
Done the next step you need to go back to the query editor and make sure that your start date and end date are set correctly so we're gonna go back to our.Model click Edit queries and if you look in the calendar query over on this side you would see this section and it's a set parameters above and really the ones we care about right now are the start.
And end date so if you click on start date right now it's set to January 1 2014 and date is December 31 2017 you can change it to whatever you want and later we're also gonna see how you can.
Make this dynamic so you do not have to set it manually so again first two steps really easy now just change it to 2018 or whichever date you prefer so start an end date change change that.
To what's needed alright so starting an end date is right and our table is set and we are ready to use so we have product inventory we're tracking units in and units out.
And earlier we could only analyze it using the product attributes right so we were analyzing let's go back here real quick product inventory and earlier without the calendar table we could only.
Analyze it using the product table and now we have the calendar table with this fool capabilities so we're gonna do something here let's try product inventory units in units out let's.
Change it to line graph and I'm gonna put in year here as a slicer oops actually let's make the list perfect and here I'm gonna use the month num and let's see how that shows just a few.
Other tweaks x-axis get a coracle I want to fix this sorting all right there we go so we a month 1 through 12 and I can check a specific year if I wanted to now that's great and.
Working but you noticed that I I stayed
Away from a lot of conventional fields like the month name and there's a reason for that because there are some other settings.Which you would need to change so we have already done create relationships and we've already done looked at the start and end date but there are two other settings you need to look at which.
Is the sort by column and don't summarize let's look at that so we're gonna go back to our table and I'm going to go to the data view in the calendar table and you notice the month feel and.
By default it's gonna sort alphabetically we don't want that so I'm just gonna change that to sort by month num and same for this and I'm gonna go across and fix the sort by column.
Settings for some of these other labels or if you add here it kind of sums it up it's adding up the years which is just silly so for that we're gonna go back to the data view and select columns like.
The ear and change the summarization setting do don't summarize so I'm just gonna check that right there and I'm gonna show you how to change the behavior so now if I select the year.
Well try table just list that out and doesn't try to add them up I'm gonna go back and fix the summarization settings so for a few other numeric columns perfect so I've changed that for pretty.
Much all the numeric columns that I saw because really there's nothing here which needs to be added up and now that we have the sort by column all correct we can have a little more fun here and.
Instead of month num we can put the real month name which is a little more friendly for all of us so that's it we copy pasted our calendar table modified a few settings and we are able.
To leverage the full so that's it we just copy pasted the calendar table and we're able to leverage the full strength of the calendar table just by a copy-paste and.
Changing a few settings now so let's go back now the truth is that this may be a great starting point the calendar table that I have provided is quite full-fledged.
As you would see there lots of columns here but I know that sooner or later you are going to customize it and here's the reason why the reality is that every organization I've been to has their own.
Unique requirements for the calendar table everybody may start off with a basic set but then they always add something on the other I remember walking into a client once gave them.
This calendar table and they said oh Abby can we throw in academic semesters they're like what University and said no but some of her audience or customers clients are educational and so we just.
Want to track how our sales or other metrics do according to the academic semesters right so and again so you're gonna have your own specific requirements so for that you are going.
To need to customize it to really make it your own so the next thing we're gonna do is I'm gonna walk you through step by step on how I created the calendar table now it's going to be a.
Good learning experience experience and it will help you make the tweaks you need to customize that calendar table just for yourself hey keep watching more videos and keep learning power bi but if.
You did enjoy this video I would love to hear from you so leave a comment like subscribe all the good stuff while we're on my friends [Music].
