Trained thousands of people from all over the world in Power BI, I started as a non techie business user quite like you, lost and a bit frustrated. So now it's my goal to make tech easy for the non techie. So make sure to subscribe and click that bell When you watch.
This video all the way to the end, you will know how to get the ultimate Power BI calendar table. Not just that, also how to set up and use the calendar table. I mean, what's the point of getting it if you don't know how to use it?.
But perhaps the most important part, which is how to modify and customize the calendar table to make it your very own. In addition to that, we're also going to point you to additional resources on calendar and time intelligence. But before all that, we need to start with why?.
Why do we even need the calendar table? Now I want to share with you the top three reasons of why you need a calendar table. Let's see if you know all of them. #3 We're going to do countdown styles, so #3 is..
By month, by weekday, by year, quarter, month, or any of the other combinations available to you inside the calendar table. Let's go to #2. And this one is about time intelligence, and that includes something like year over year comparison..
Or maybe you're trying to show 1/4 to date, year to date data. Or maybe you're doing rolling periods or something else equally fancy and amazing in Power BI time intelligence, and you're going to use that. Do that by using the DAX time intelligence functions..
DAX time intelligence functions are fun and easy to use, but guess what all of them have in common. All of them, as the first parameter, expect you to pass the date column from a valid calendar table. So that's a great reason to have the ultimate calendar table.
Inside your model. All right, let's talk about #1. Now this one is a little subtle and it requires a little bit of understanding of Power BI modeling. But if you have been around Power BI, you'll probably run.
Into this scenario. And this is perhaps one of the most frequent questions that I get, especially from users new to Power BI, which is they ask me Avi, I have these two different metrics coming from 2 separate tables..
- Excel Today - Cross Functional Charts in Visio
- School Graphics Prints Files Free Download Sheri Sk Prints Graphics Office
- How To Design A Responsive Portfolio Website Using HTML, CSS & JavaScript Part 5 BlogSpot
How can I show them on the same chart or the same graph? - ID Card Make
Now this could be sales or budget, or this could be something totally different. Tasks support calls, Warehouse, Shipment. Doesn't matter what your data table is you're tracking,.Sometimes you're going to need to show metrics from separate tables side by side. How do you do that? Of course, Calendar table is one of the easiest ways to link two data tables together, and you can do that simply by importing.
The ultimate calendar table inside your model. We're going to talk about that and then connecting that to your data tables by using relationships. So that's the number one reason for requiring or needing a calendar table or date table inside your power BM model to.
Connect two distinct data tables. So now we have an understanding of why we need the calendar table. Let's talk about where you can get the ultimate calendar table. And as I had promised, you can get the Ultimate Calendar Table.
In just three easy steps. Step one, go to this URL on this page and download the Ultimate Calendar Table. Now you're also going to find the link in the corner or in the video description below..
Once you download the file, I want you to open that Power BI desktop file or PBIX file and then open the Query Editor by clicking the Transform Data button. And step three is simple. Copy and paste..
Make sure the Power BI desktop file that you're working with your own PBIX file. You also open the query editor on that model and then simply right click and copy the calendar table from the file you download from that page and paste it..
Right click and paste it in the query editor of your own model. That's it. Easy as 123. But of course that's not going to be useful to you unless you know how you can set up and start using the calendar table,.
But that part is pretty easy as well. The most important step that you need to take is to set the sort by column. Now if you forget this step then what you're going to find is that the month names and weekdays are going to be sorted.
Alphabetically, which is I'm sure not what you're looking
For. So just go to that specific column, for example month, and select that column and then select the sort by column and choose the numeric month or month NUM..And you can do that for other fields like weekday as well. And the next step is going to be to create the relationship between your data table and your calendar table, which is now going to act as a lookup table. Now if you go to the download page, you're going to find a.
More detailed video with step by step walkthrough of how you can get the most out of the calendar table. But for this video, we still have some exciting stuff to talk about. So let's talk about that..
And this is how can you modify and customize the calendar table and make it your very own. Now, it's quite likely that once you start using the calendar table, you're going to run into one of these scenarios. Maybe in your organization, the year starts in a different.
Month. Maybe it starts in April or July or some other month. There are a lot of organizations which follow that fiscal year or that shifted year. Or maybe you want to adjust the week so that it doesn't start on.
Sunday by default, it starts on a Monday. Or maybe you have specific calendar columns that are used within your organization or some other customization that you need. Now I'll share with you that I have worked with a lot of.
Different companies across the US and all over the world. And this, the answer to this is always, yes. There's always been some customization, some tweak that needed to be made to the calendar table for it to be really useful for that organization..
And that, my friends, is the secret strength. And that is why this is the ultimate calendar calendar table. Now, you may already be aware that there are a few different ways that you can build a calendar table..
You can use the auto date time functionality, you can use DAX to create a calendar table, or you can use Power Query to create a calendar table from scratch. Now, I have covered each of these in detail in a separate video..
You can watch that video if you're curious. But in this video I'm going to save you time and I'm going to cut to the chase and tell you that this is the best way to create your calendar table. Why is that?.
Well, if you have been around Power BI or if you watched my 60 minute tutorial, then you already know that the query editor is the kitchen of Power BI, right? That is where you clean, shape, and transform data. That is where you got the ribbon fill full of tools and gadgets.
All available to help you with that cleaning, shaping and transforming. And we also know that it's so easy to use, use. It records every step. You can go back and insert steps, change steps..
It's really user friendly. And really it's the first thing that most people fall in love with when they start using Power BI. And that is what makes this the ultimate Calendar table. Because this is built 100% inside the query editor, it.
Doesn't connect with anything else. It builds the table from scratch inside there. Now there is a lot of goodness already built into the calendar table. Once you download and open the table you're going to see..
This already comes pre built with year, quarter, month. It comes with a fiscal month, quarter, year. It starts in July but that is adjustable. You can customize that easily inside the query editor. It has a concept of current month, current quarter, or year.
Offset which lets you do things like only always show me the past three months or the past three weeks, or only show me they always show me in the current year. It has a weekday weekend indicator, It's got the Future Past date indicator that can really help you in some.
Calculations and display. It's got a month to date, quarter to date, year to date flags that can help you in different calculation scenarios, and a whole lot more. All of that is baked in and again, all you have to do is.
Follow those 3 easy steps. Copy, paste and all this magic is going to be at your fingertips. But the best part? It's built in the query editor..
That means you can open the query editor, examine it step by step, and change any step you want, or add any steps you need, and edit the calendar table based on the customizations you need for yourself and your organization. And that's how you're going to make it your own..
Now you can get more from the download page. We'll have additional videos and resources on how to use the calendar field. Some of them I just talked about right now, some more pointers on how you can customize the calendar table and more it will.
Point you to more bonuses around calendar usage and time intelligence. So go to that URL and get the ultimate calendar table right now. So we have covered everything that we promised to you at the.
Start of this video. Now it does have chapters built in, so if you need to review a specific section you can jump to that using the chapter functionality. And again, here's the URL where you can download and get the.
Ultimate calendar table. My goal is to make tech easy for the non techie. If I helped you with that today then leave a comment and please let me know. I'll see you in the next one..
