For dates such as years quarters months and days and look at how you can also create text labels for those dates when we create text labels we'll need to look at how to sort those.
Chronologically rather than alphabetically and when we've done all that we'll look at creating a relationship in the data model between your custom calendar table and the.
Original date time field how you can create hierarchies to make it easier to add combinations of date intervals into a visual and finally how to use a basic time intelligence.
Function using your new custom calendar table so quite a lot to do in this one let's get started to get started i've created a new power bi report and then i've imported all the.
After importing it i've tidied up the data model a little bit if i just switch into the model view you can see that i've hidden many of the id columns just to avoid those cluttering up the fields.
List back in the report view i've also just rearranged the tables a little bit to make things easier to read the data that i've imported contains two date columns as a release date column in.
The film table and a date of birth or dob field in the director table because i had the auto date time feature enabled when i imported this data each of the date columns in the data.
Model automatically create a hidden date table in the background and you can get an idea of that if i switch back to the report view if i expand my film table for example.
And then look at the release date field i can drill through into that to see a built-in date hierarchy consisting of the year quarter month and day those fields definitely don't appear.
Posts Related:
- SQL Server Programming Part 1 - Stored Procedure Basics
- Planilha Controle de DÃzimos e Ofertas - Atualização
- Drive efficiencies in loan processing for the banking sector with Microsoft Power Automate
Separately in the data model and they're
Definitely not included in the original excel workbook either those extra fields are created automatically by the auto date time.Feature now the auto date time feature is a pretty handy thing to have available we've done lots of clever things with it in the previous few parts of this series.
Working with the date time intelligence functions and you can use it to do very simple things as well if i just quickly head into the report view and add a basic.
Matrix i can drop the entire date hierarchy into that matrix and then quickly summarize let's say the runtime minutes field without having to do too much hard work at all and automatically.
Get some nice drill down features so i can group my data by year quarter month and day now there's nothing inherently wrong with the built-in.
Auto date time calendars but they are a little bit limited if you want to do any sort of customization you can't even see the built-in calendar that power bi has created for you or at least not within.
Power bi desktop at least so what we're going to do in this video is show you how to replace the auto date time calendars with your own custom ones and the first step in doing that is to.
Disable the auto date time feature to get rid of the built-in calendar that's been created for us so to do that we can head over to the file menu.
And then choose options and settings followed by options and there are two places you can disable the auto date time feature one is at a global level so in the.
Global section in data load if you want
To prevent the auto calendars being created for future files you can uncheck this box here if however you just want to disable it.For the file you're currently working on you can head to the current file section and then choose data load and then there's a separate time intelligence feature there i'm going to disable that.
Auto date time checkbox and then click ok that'll make a bit of a mess of our data it's going to get rid of the built-in date hierarchy for the release date and.
It just reverts the release date field to showing the individual release dates for our films now that we've removed the automatic calendar tables we can replace those.
With our own custom versions and if we want our custom calendar tables to be compatible with dax time intelligence functions we need to follow a few simple but important rules when we.
Create them so if we take our release date field for example i want to create a calendar table that i can use to analyze the release dates of our films.
To do that i need to create a table that contains a column of dates and the range of dates must go from the first day of the first year represented in the column i'm interested in so in.
This case the first film was released on the 27th of january 2010 so my calendar table must begin on the 1st of january 2010. the last year represented in that column.
Is 2016 so the last date in my calendar table must be the 31st of december 2016. i can't have partial years i also need to make sure that every single date between the beginning and.
The end is represented so although the release date schedule has plenty of gaps in it we're jumping from the 27th of jan to the 5th of feb i need to make sure that the calendar.
Table has a row for every single date in between once we've added the main column of dates we can add as many extra columns as we need to allow us to group our.
Dates by different intervals such as the year quarter month and day that you see in the automatic calendar tables now it's worth considering that you don't necessarily need to create your.
Custom calendar tables in dax in power bi you could happily create a custom calendar as part of the original data source.
So for example all of our movies data comes from an excel workbook excel has plenty of useful date time functions which would allow you to create a custom calendar table and then.
Simply import that as part of the data model however as this series is meant to be about dax and power bi i'm going to show you a couple of dax functions for.
Creating a custom calendar table just to help see the results of this i'm going to head over to the data view first and then from the table tools tab in the.
Ribbon i'm going to click the new table button which allows me to write a dax expression for creating a new table there are two main functions you can use to do this one's called calendar auto.
And the other is called calendar i'm going to show you the calendar auto function first just to demonstrate why this might not be the best choice particularly when you have multiple date.
Columns in the same data model so let's call this one let's call it release calendar and then we'll make this one equal to the calendar auto function.
There is a single optional parameter for fiscal year end i'm going to ignore that for the time being and just close the round brackets for calendar also and then hit enter to create my new calendar.
Table you might just about be able to make out there but it's created a pretty wide range of dates for me the first date is created it's the 1st of jan 1930.
Now obviously our films only range from the from january 2010 but don't forget we've got a second date field in our data model which is the director's date of birth.
And the earliest year in which a director was born was 1930 so we get the full range of dates from 1930 all the way through to 2016 which is a little excessive if you ask me.
So i'd like to change this so that we're restricting the range of dates that our calendar works with i'd like to create a range of dates starting on the 1st of jan 2010 and.
Ending on the 31st of december 2016. so i'm going to edit this formula to use not the calendar auto function but the calendar function instead now you can see there are two compulsory.
Parameters the start date and the end date and just to do this in a simple way to begin with we'll tidy this up and make it look a bit neater in a moment i'm.
Going to use the date function to generate the dates i'm interested in so i'm going to say date 2010 comma 1 comma 1 and then the end date will be provided.
With another date function using 2016 comma 12 comma 31. so having done that if i now hit enter to create that updated version of the table the first date that we've got here.
Is the first of jan 2020 sorry 2010 and the last date should be the 31st of december 2016. next i'd like to make this expression a little more flexible so that if we'd.
