Dates and that's going to involve writing either date literals or using the date function to construct a date out of its separate parts we'll also look at how you can calculate future and past dates by adding or subtracting days to your dates and then at the end of the video we'll look at how you can work out the difference between two dates either by subtracting one from the other or by using the date diff function so plenty to do in this one let's get started to get started i've created a new blank.
Power bi report and just before we choose to import some data i'd like to check out one of power bi settings related to dates and times so we'll head up to the file menu and then choose options and settings and then choose the options option on the right hand side and when the dialog box finally loads we'll have a look on the data load page of the global section so down towards the bottom of the page here is an option to do with time intelligence and this little check box about auto date time for new files.
Now you can read an awful lot of detailed information about this by following the learn more link to the microsoft documentation but basically all you need to know for this video is that if that setting is enabled when you import data into your data model power bi recognizes date time columns and will automatically generate a background calendar table for each separate date time column that it finds and this provides quite a few advantages for working with dates and times in your visualizations.
So the first example we're going to create is going to rely on that automatic calendar that's created so i'm going to make sure that this option is checked bear in mind that if you've had to change this setting just now the change will only affect new files that you create so it won't affect any existing report that you had open so with that setting enabled i'll click ok and then i'm going to choose to import some data from an excel workbook i'll double click on the movies workbook and again as usual i'll drop a link in the video description so you can download that file yourself.
And then when the import wizard loads i can check the box next to the movies worksheet click the load button and we'll find that when the data has loaded if i expand the fields list on the right hand side i can spot the single date time column in that data set with the little calendar symbol next to the release date column name this automatic datetime feature provides lots of advantages for working with dates in the report view although you.
Visual by clicking on the matrix tool in the visualizations panel i can now drill down into each individual year and then each individual quarter in the year and month in the quarter to see a breakdown of values based on days months quarters and years that is reflected as well in a slightly different way in a chart so if i change my matrix into a clustered column chart at this point we'll need to include some data to display so let's include the box office field and then i'll just change.
The aggregation there to an average rather than a sum so at this point we're showing an individual column for each year but if i enable the drill down feature i can then click on a column for a year to see the quarters of that year and then click on a quarter to see the months and then click on a month to see the individual days and then i can drill all the way back up again to see the data at the year level.
I don't have to see this data broken down by this date hierarchy by the way if i wanted to see the individual dates it's fairly simple to switch from the hierarchy to the individual dates so once you've added the release date field to a visual you can click on the drop down arrow next to it and choose to show the release date column rather than the date hierarchy so that will now show a separate column for each individual date in my data model i can then go back to the hierarchy by clicking the drop down.
Arrow next to the release date and choose date hierarchy again i can also choose individual parts of the date hierarchy if i wanted to i could show this data broken down just by month for example by removing levels of the hierarchy by clicking the crosses next to them so now i'm showing a breakdown of average box office by month of release i don't have to add the release date column first i could pick a specific level of the hierarchy when i first build the visual so if i get rid of the release date column from the axis bucket.
Entirely you can expand the release date and then the date hierarchy contained within it to select exactly the level that you want when you build your visualizations so lots of advantages there for working with dates when you've got this automatic date time feature enabled now let's compare this report with one that we create without the auto datetime feature enabled just before we do that i'm going to save this report and give it a sensible name something like auto date time or auto calendar something.
Like that and then save that and once that's finished we'll head up to the file menu and we'll choose
Options and settings options and then we'll temporarily disable the auto date time feature now you may be wondering why i'm choosing to disable this feature of it so useful and there are several reasons you might consider doing this the main one is probably if you're choosing to create your own custom calendar table to work with the date time intelligence features of power bi.That's something we're going to talk about in a lot more detail in later videos in this series it's also worthwhile bearing in mind that the automatic date time feature only works under certain circumstances and you can read a lot more about those in the learn more link in the microsoft documentation so it's definitely worthwhile learning how to work with dates without the automatic date time feature so let's click ok and then we can choose to create a new report so i'll add to the file menu and choose new or i can just press ctrl and n on the keyboard.
And when the report has created itself let's close down the splash screen there we'll choose to import just the exact same excel workbook so let's import the movies workbook and then when the import wizard is loaded we can check the box next to the movies worksheet and then choose to load that data and when it has finally loaded we can expand the movies table on the right hand side eventually there we go and you'll see we have imported the release date there it.
Is if we look at the data view it doesn't look any different in the data view it just looks exactly the same as the previous example but of course this time you'll notice the lack of little calendar symbol next to release date and if i did check the box next to release date it creates a table but it doesn't break that down into the individual component parts of the hierarchy because that hierarchy doesn't exist so let's say that we wanted to recreate that date hierarchy the first thing we'd have to do is calculate the individual.
Bits of the date that make up the hierarchy and that's actually relatively straightforward because dax has some built-in functions for separating dates out into their component parts so let's head back to the data view and we'll start by calculating the year from the film's release date so we can create a new column and i'm just going to call it yeah let's just zoom in so you can see a little more clearly what i'm doing so i'll create a new column called year and i'm going to use a function also called year.
So we'll say yeah open some round brackets and then we need to pass in a reference to the release date column we can then close around brackets and hit enter and now we've got the year components created already we can continue that theme if we create another new column we'll create a column called quarter and i'll make this equal to you've probably guessed it already the quarter function and pass in the release date close around brackets and hit enter and there's the quarter i'm going to create the next column i'm.
Not going to call it month i'm going to call this one month num or month number and the reason for this is that when we use the month function which you may well have predicted already the month function returns a number from 1 to 12 so we need a slightly different way to get the month name but let's start by calculating the month number this is still going to be a useful piece of information so we'll include the month movies release date and then to get the name of the month we're going to use a function you may.
Have seen in the previous video the format function so let's create a new column called month and we'll make this one equal to format open some round brackets and then there are two parameters to fill in we'll refer to the release date first and then the format parameter in some double quotes i'm going to format this with four m's to get the full month name that will be january february etc three ms would give you jan feb etc so if you prefer a short month name then go for three m's four will give you the.
Full name close around brackets and hit enter and we'll get the month name and then finally in terms of calculated columns i want to calculate the day of the month so one more new column and we'll call this one day we'll make this one equal to and once again you've probably guessed the day function so we can pass in a reference to the release date close the round brackets and hit enter and then at that point we've got all the components of the.
Hierarchy that we need next we can create the date hierarchy
And probably the easiest way to start with that is to right click on the year field in the fields list and choose create hierarchy so you'll see that gets added into the fields list and you can expand it with a little arrow symbol to the left of it to see that you've got the year field sitting within it let's just rename the hierarchy before we add any more fields to it i'm going to double click on its name and i'll.Call this one release date hierarchy i'd like to call it just release date but of course that name's already taken by the original column so once i've renamed it we can add more fields to it by right-clicking on the fields so let's go for the quarter field next add to hierarchy release date hierarchy and then just do the same thing with the month field and then the day field as well so you'll see all those fields added into that hierarchy finally just to give it a quick test.
Let's head back to the report view and in this table that's currently showing the release date field in the values bucket let's get rid of release date and replace that with your release date hierarchy so just as we did in the other example check the box next to the hierarchy to add all those separate values into that table there are a few extra things we can do to make this example a little more similar to the example we created earlier with the automatic date time feature first of all i'd like to hide the separate columns that appear in the.
Fields list in the report view we can access all of these from the hierarchy itself so it's pointless seeing them listed twice we can change that in a couple of different places we could go back to the data view and then next to each one of these individual columns we could click the little i symbol to hide it from the report view when we have multiple columns to hide it's probably a little easier to select multiple columns and hide them in the model view so if we switch over to the model view and then select the first column day.
Then hold down the control key and click on each of the other calculated columns that we wanted to hide we can then click the single i symbol and hide them all at the same time so if we switch back to the report view now we can see that those additional columns are hidden from the fields list making it a little easier to work with next i'd like to improve the appearance of the value displayed in the quarter column in the auto date time example if i just give myself a bit of space to insert a new table if i insert the quarter column it's shown with the.
Letters qtr in front of the number so to make that work if you remember from the previous video we can concatenate values together to form larger bits of text so from the report view i could highlight the quarter column and then if i just zoom in again on this formula bar we could add the letters qtr followed by a space in some double quotes and then use the ampersand to concatenate that with the number generated by the quarter function and if we update that expression now we'll see a slightly more.
Readable value for the quarter one final improvement i'd like to make is in the way the month column works if i just switch back to the auto date time example we created earlier and if i get rid of the quarter column from that table and then just add in the month column instead you can see that sensibly it's sorted chronologically from january through to december if i try to do a similar thing in our new example if i click into a blank part of this page and then just insert a.
Table and then if i choose to include the month column i could then get rid of the year and the quarter you can hopefully see that the month names are sorted alphabetically from april through to september which isn't particularly useful so the reason we created the month number column earlier on if we head back to the data view we can select the month column and then from the column tools tab we can choose the sort by column option and choose to sort the month name by the month number.
So once we've done that if we head back to the report view we can see now that the month names are sorted chronologically from january through to december so that was quite a lot of effort to recreate something that in most cases power bi could have created for us automatically so let's look at a few things that the automatic date time feature doesn't do for us we'll start with a simple example of adding a different interval of time i think it would be nice to be able to.
Analyze our films based on which day of the week they were released on and that's not something that the auto date time feature creates so let's head back to the data view of our report and then the way we do this is kind of similar to how we did it with the month number and month name i want to add in the month names of course so we can see monday through sunday but have them sorted chronologically rather than alphabetically so let's create a new column to calculate the number of the day of the week so let's call this one something.
Like weekday num equals and then we'll use the weekday function now there are two parameters here the first one is a reference to the release date and then the second one is what day you want to consider the first day of the week so we have a few different options here we can choose sunday as one through to saturday or monday as one through to sunday or monday as zero through to sunday at six um i'm going to go with monday one through sunday seven.
So i always consider monday the first day of the week so we can then close the round brackets and hit enter and that'll give us the weekday number creating the weekday name is fairly straightforward we can just use the format function so let's create a new column and we'll call this one weekday and we'll make this one equal to the result of the format function using the release date and this time we'll use the code ddd to get the full name of the day of the week close around brackets and then hit enter.
And there's our day names with that column selected i'm then going to choose the sort by column and sort this by the weekday num finally i'm going to make sure that the weekday num column is hidden from the report view so i only need to do this for a single column now so i can click the i symbol next to the weekday num column in the data view if we now head back to the report view let's just use our existing table let's.
Swap out the month field from the hierarchy let's replace that with the weekday field and then we could add in the let's see let's go for a count of the film id if i add in the id column that will automatically give us a count and then perhaps we could change that into a clustered column chart to see a slightly more interesting view of the results so no great surprise that many films are released on a friday i think next let's have a look at how we can use dates in comparisons i'd like to divide my films into old and new films.
Depending on whether they were released before the 1st of january 2000 and yes i appreciate it is a bit of a stretch to consider 21 year old films as new but it's just for this example the first way i'm going to do this relies on a feature that was introduced in the august 2021 edition of power bi desktop if you're not sure which version of power bi desktop you're currently running head to the help tab in the ribbon and choose about and then it shows you exactly which version you've got installed so i'll show you the technique that will.
Work for versions prior to august 2021 if you're not already running that version but for the first example let's head back to the data view and we'll create a new column and i'm going to call the column old or new equals and then use an if function to test if the release date column has a value that's less than and then this is the thing that only works from august 2021 or later if i type in the letters at dt and then in some double quotes i can type in a.
Date literal i must do it in a particular format a format people who use sql should be fairly familiar with start with the year followed by a dash and then the double digit month and then another dash and then the double digit day as well so that's the 1st of jan 2000 if i type in a comma and then shift and enter on the next line i'll describe that sort of film as an old film followed by a comma and then on the next line we'll say new when i close around brackets and hit enter we'll see a combination of old and new values in this column and if we.
Switch back to the report view we could add that field into our existing chart perhaps let's add it to the legend bucket and see if there's a difference for day of the week of release according to whether a film is new or old it appears to follow the same general pattern so how do we do the same thing if we're using an earlier version of power bi desktop well we can't write a literal date into an expression i'm going to select the old or new column in the fields list i'll just stay.
In the report for you to make this change and then rather than writing the literal date preceded with letters dt we can use the date function so if i open some round brackets after the date function i can specify the year followed by the month followed by the day and you must use numbers for all of those components and then when you put all those values together the date function produces a complete date from those individual values so if i hit enter again we'll end up with exactly the same results but this one will work in whichever version of.
Power bi you're using next let's look at how to calculate future and past dates in dax and i'd like to start by looking at a function you may be familiar with from other microsoft products called date add there is one big difference with data in dax that i'd like to show you so to demonstrate this i'm going to go back to the data view i'd like to be able to see the results of my new column here and we're going to imagine that each film has a limited run at the cinema of 28 days exactly so for each.
Film's release date i want to calculate the date that's 28 days in the future and that's great because it lets me use a silly movie related pun so i can create a new column called 28 days later and we'll make this equal to the date add function we specify first of all the column containing the dates we want to add or subtract time to or from so that's movies really that release date followed by a comma and then the number of intervals if we wanted to to subtract days then we just use a negative number.
Of course here we want to add days so i'll use the positive number 28. follow that with a comma and then say what interval of time we want to use so in this case it's going to be a day if i press enter now the results are slightly unexpected while some films do calculate a date that is 28 days from the release date many films don't and here's the big difference with the date add function in dax in order to return a date that date must exist in the column you specify here.
So the date add function is actually more much more useful when we use the date time intelligence functions so when you have a proper calendar set up that your date fields are connected to the data function becomes much more useful and again we're going to be covering that in more detail later on in this series so the easiest way by far to get this to work is not to use the date add function at all let's get rid of the most of this we're just going to arithmetically add the number 28 onto.
The movie's release date so much like in excel you can treat dates just like numbers and you can add and subtract numbers to and from them the value of 1 represents a full 24-hour period so if i say add 28 and then update this formula it calculates 28 full days from the movie's release date of course the number of days that you add or subtract doesn't just have to be a hard-coded number like this that value can come from a column so for example.
Let's imagine that the run time column wasn't the runtime in minutes of the film it was the length of time that the film was released at the cinema so we could add the value of the runtime column to this release date by saying release date plus movies runtime and that will calculate a date there's a variable number of days in the future but that doesn't really match any longer the title of this column so let's uh change that back to 28 just to complete that example we can use similar basic arithmetic to.
Work out the difference between dates so let's say we wanted to work out the age of the film in days we could subtract its release date from today's date let's do that using a new column we'll call this one age in days and then we'll make this one equal to the date function let's say we wanted to use a single specific date first of all so we'll say date 2021 comma 8 comma 24 to construct today's date and then subtract from that the release date of the film now when we hit enter the end result.
Doesn't look like the difference in days it presents the result as a date time value if you wanted to see what the underlying value of that formatted date was we could change the data type of the column from date time into a whole number we'll have to confirm that we want to do that so it shows us that the first film at least is 5177 days old one of the downsides of the technique.
We've just used here of course is that that date won't ever change it's a fixed point of 24th of august 2021. it will be a little more useful if we could calculate the current date and that that would update when we refresh our data model so to do that we have a couple of functions we could use again you may be familiar with the today function from excel so there's today there's also a utc today so the coordinated universal time so today uses the the time zone that you're in or at least the time zone that your server is in.
So i'm going to use the today function open and close and round brackets there are no parameters for this function it's just today open and close around brackets hit enter to update the formula and we'll see the same end result the difference is that when we refresh this table the today function will recalculate to give us the current date now there is actually a function in dax designed to calculate the difference in dates again it may be one you're familiar with from other microsoft products called date diff let's use the date diff function to.