Applied in power bi's options so the first job is to head up to the file menu choose options and settings then select options and in the dialog box which appears we're going to use the current file section and the data load category and we're going to look for the time intelligence section in there and look for the auto date time property we're going to make sure that this box is checked and what this is going to do is make sure that whenever we import data containing dates and times power bi will automatically create a date or.
Calendar table linked to that date column this is really important for all the time intelligence functions to work this calendar table is required you can create your own custom calendar tables and in a later video in this section of the tutorial we're going to look at how to do exactly that but for this introductory video we're going to use the automatic calendar table so make sure that that box is checked and then we can click ok and then we're going to import the data from the excel workbook so we'll click import data from excel.
And the workbook i'm going to use here is similar to the workbooks we've been working with in previous videos in this series although i've filtered out a lot of the data so we're only going to work with films released between 2010 and 2016. i'm going to double click on that file to start importing it there are two tables in this workbook so i've simplified the design of the workbook so that all the data is contained in a single table called movies we've also got a blank all measures table just to save a bit of time creating our measures table.
So with both tables checked we can click the load button that's going to import all the data into the basic data model okay and now that's done we've got all the data we need to start working with the time intelligence functions the movies table we've just imported contains a single date column called release date a neat way to check whether your auto date time feature has worked is to find the little arrow symbol to the left of your date columns click on that to expose the dates hierarchy contained within it you can expand the date.
List the last date is the 21st of december 2016. the range of dates we have here is not continuous you can see there's a gap between the first and second dates 27th of jan to the 5th of feb and some of the dates in our data model are duplicated as well so these two films here were released on the same day 26th of march 2010 for the time intelligence functions to work the calendar table or the date table must contain a continuous unbroken range of dates.
So we can't have gaps between the dates in the date column the range of dates must begin on the first day of the first year of the dates in that release date column so i must begin on the first of january 2010 and the date list must end on the last day of the last year included in that column so 31st of december 2016. i also need to make sure that the date column in the calendar table doesn't contain any duplicates each date must appear once and once only.
If you're familiar with database design you can consider that date column to be a primary key for the table just to demonstrate that that's happened for us automatically we don't actually have to worry about this but it's important to understand what this calendar table is doing if we head back to the report view we can insert a basic table onto the report page it's going to collapse the filters panel there then i'm just going to add my release date field into that table you can hopefully see that the first date there isn't the 27th of january.
2010 it's january the 1st 2010 and i have a separate row for each individual date a continuous unbroken range of dates all the way through to the 31st of december 2016. if i just quickly change this in the field well here if i change from displaying the date hierarchy to displaying the original release date field i can click the drop down arrow there and choose release date that goes back to showing the actual.
Dates that we've imported switching back to the date hierarchy shows the continuous and broken range of dates contained in that automatic date table so now that we've established the date table has been created correctly let's start making use of some of its features i want to start with a basic measure to show the average runtime of films released in each year so let's add the basic measure first of all in the all measures table i'm going to right click and choose new measure.
- Relatório semanal, mensal e anual do Excel Office
- How to Design Cover Page in MS Word DIY Tutorial
- Reporting Services (SSRS) Part 5 - Controlling the number of rows per page
And then when the formula bar appears let's call this first measure avg runtime - ID Card Make
And we'll make this one equal to the result of the average function looking for the movie's runtime field we can then close around brackets and hit enter and then i'm going to change the design of the table that i've just created here i don't want to display all four levels of the hierarchy i only want to show the years i can do this in a couple of ways i can click the crosses next to the day.And month etc to get rid of the levels i don't want or i could get rid of everything and then just drag the year field into the values bucket so that i only have the years displayed then i'll add the average runtime field to the same values bucket and then just to tidy things up a bit i'm going to delete the delete me column from the all measures table and then confirm that i want to do that and then hopefully to make things a little easier to read on screen i'm.
Going to select the table head to the format panel and then for the value section i'm just going to bump up the values font size to about 13 or so just hopefully make things a little easier to read so there we go our first basic measure grouped by years next i'd like to compare the average runtime of each year with the previous year and to do that we're going to need to manipulate the filter context applied to the measure in this table as you'll know if you've watched.
Previous videos in this series the result produced by that same measure that same basic aggregate function is affected by the location of that measure in the visualizations in the power bi report so it's fairly intuitive i think in this particular example that the average run time there comprises only the runtimes for films whose year of release is 2010 and similarly for 2011 and 2012 etc so we need to manipulate that filter context to look at the dates one year.
Ago to do that we're going to work with the calculate function if you haven't already followed along with the previous section of this tutorial series that talks about how to use the calculate function to manipulate filter context then it's worthwhile revisiting at least the introduction video there the calculate function here's what we're going to do back in this report i'm going to create a new measure in the all measures table and i'm going to call this one avg runtime i'm going.
To call it previous year and we're going to use the calculate function to do this we could write our average function in here again to recalculate the average runtime but as we've already done that in an existing measure let's just refer to the average runtime measure then we need to manipulate the filter context to take it back one year there are several ways we can do this but i'm going to introduce you first of all to a fairly general purpose date.
Time intelligence function called date add date add lets you add just the time filters or the date filters forwards or backwards using different intervals of time so there are three parameters to fill in for the date add function the first one probably the most important i think is a reference to the column containing the continuous range of dates so this is where our auto date time calendar table becomes important.
We want to reference first of all the release date field in the movies table but it's not the original release date column that i'm interested in as we've established that one doesn't contain the continuous and broken range of dates i want to refer to the date property associated with that so when you've got this auto date feature enabled any date column that you reference in dax code provides you with access to.
These extra properties so to reference the date column after referring to movie's release date i can say dot date to refer to that key column then i can type in a comma and then i can say how many intervals i want to move forwards or backwards so to move backwards in time by one year i'm going to say minus one and then another comma and then i've got four options as you just saw there pop up in this little intellisense list if you find the intellisense list.
Disappears you can press ctrl and spacebar to try to force it to reappear so you've got four increments of time
Which corresponds to the four levels of the hierarchy you'll have seen created in the auto date hierarchy i'm going to go with the year interval for this example so having done that we can close two sets of round brackets one for the dates add function one for the calculate function and then if we hit enter we could add that new measure into.Our table so the date add function that we've just used there takes all the dates included in any particular filter context and moves them all back by one whole year so in this particular example here in this row of the table the filter context takes the range of dates from the 1st of jan 2011 to the 31st of december 2011. moving them all back by one whole year gives you the 1st of jan 2010 to the 31st of december 2010..
This applies to different increments of time as well of course if we add some more levels of our date hierarchy let's add in the quarter field into the date hierarchy of that table and you can hopefully see here that the filter context on this row now is quarter one twenty eleven so that's everything from the first of jan 2011 to the 31st of march 2011. when we shift that date range back by one whole year we get the 1st of jan 2010 through to the 31st.
Of march 2010 so you can see that this line here corresponds to this line here this one here to this one here etc we can break that down even further we can go into months as well don't think we've got quite enough data in this movie's database to have a value for every single month but we can still see hopefully that for 2011 quarter one january the range of dates we're working with in that particular filter context is the 1st of jan 2011 to the 31st of jan 2011..
In our measure we've shifted that date range back by one full year so we get the first of jan 2010 to the 31st of jan 2010 and i appreciate that i'm kind of labeling the point here a little bit but it's always important to remember that when you're using the time intelligence functions you're not referring to the original dates you've imported you're referring to the continuous range of dates contained in that calendar table i think a nicer arrangement for this particular set of data might be to exclude the month just because we don't have data.
For every single month combination um i'm going to get rid of the month field from my release date hierarchy i'm going to change this table into a matrix and this is a bit nicer because we get the roll up for the matrix here so we can see the various different levels of the hierarchy if we just expand everything by clicking this split arrow button here to expand all the levels of the hierarchy we can see a nice summary so for uh 2011 you can see the total.
Sorry the overall average for everything in 2011 against the overall average for everything in 2010 but you can also see that broken down by quarter as well i guess the final step of this would be to compare the two values we've calculated so we should subtract the previous year average from the current year average so let's maybe create one more basic measure to demonstrate this i'm going to add a new measure and we'll call this one avg runtime.
Versus previous year and we'll make this one equal simply to the average runtime minus the average runtime previous year so we hit enter at that point and then we can add this field into our matrix and see whether each year or quarter of that year has longer or shorter films on average it probably doesn't make sense to show a.
Value for this measure when there is no previous year's value to compare with we can easily eliminate this using a combination of the if and is blank functions so let's wrap an if function around this existing expression and we're going to use the is blank function combined with the not function so inside the not function we're going to check is blank and then in some round brackets we'll refer to the average runtime measure and we'll use the or operator so that's.
A double vertical bar and say is blank and inside there refer to the other measure average runtime previous year i'm checking both here so that if there was a particular quarter for example with no set of values then we wouldn't show a comparison for that either so if that condition returns true so if neither of our two measures return blank then we'll show the result of the comparison.
We can leave the third parameter of the if function blank to return a blank so we can just close the round brackets there and then update that measure and once we've done that if we can just show that list there we can see there's no value when there is no previous year to compare with just to bring back the month uh level of the hierarchy again just so that you can see if i expand that down to every possible level when there is no previous month to.
Compare with so for october there's no october films in 2010 so we should see that in october 2011 there is no comparison value but also for 2011 january there were no films released although there were films released in january 2010 but because there aren't sufficient value to compare with then we have a blank there as well as i mentioned earlier the date add function allows you to move forwards and backwards using different intervals of.
Time so for the next example let's say we wanted to compare the average runtime not with the previous year but with the previous quarter instead to do that we can start by copying and pasting the original average runtime previous year measure so let's select that one and then just copy all the code from there and then we can paste that into a new measure and of course we'll change the name of that so it's previous quarter rather than previous year and then all we need to do is alter the interval of the date add function from.
Year remember you can press control and spacebar to redisplay this list of constants i'm going to pick the quarter option so having done that we can update that new measure and then we could add this one into our table or matrix and we should be able to see that we're returning the value from the previous quarter for each filter context to complete the comparison we could copy the average runtime versus previous year measure and then create a new measure.
Based on that pasting in all the code we've just copied and then change the measure name so that it's versus previous quarter and then just update the references to these different measures here so average runtime previous quarter and then average runtime previous quarter there as well so if we update that measure and then insert that into the matrix we've got the same style of comparison but going back one quarter rather than one whole year.
When you're displaying and comparing different levels of a date hierarchy you can occasionally see some slightly unintuitive looking results at first glance if we have a look at the 2010 row in this matrix we're showing a value for the average runtime for the previous quarter and at first glance that doesn't initially make a lot of sense because as we've well established by now the earliest date available in our date table is the 1st of january 2010. the thing you have to remember is what range of dates is included in the filter.
Context for that particular item in the visual so the filter contacts here is for the year 2010 this means that this range of dates isn't just for a quarter when we go back by a previous quarter it doesn't just look at the previous quarter which doesn't exist it's the entire range of dates the 1st of jan 2010 to the 31st of december 2010 that gets shifted back by one whole quarter.
It might be easier to demonstrate with a quick little diagram i've whipped up in excel if we imagine that this is this blue rectangle here represents the range of dates covered by the filter context of the 2010 row i haven't bothered listing out the individual days i've just limited this to months quarters and years when you use the date add function to go back by one quarter it's this entire range of dates that get shifted back.
So we'd effectively go from the beginning of quarter 4 to 2009 to the end of quarter 3 2010 now as we know the year 2009 doesn't exist in our date table so we're actually only going from the 1st of jan 2010 up to the end of quarter 3 in 2010 so we're getting the average runtime of any films released between those dates which is why we can see a value there for the average runtime for the previous quarter i think this is another example of.
Something that doesn't make sense to display i don't want to show the average runtime shifted back by one quarter for that year i only want to display a value here if i'm looking at a quarter or a month level one way we can solve that problem is to add an if statement or an if function to the original measure average runtime previous quarter so if we select that we could check what level of the hierarchy we're currently filtering.
If we just think briefly about these different levels this row here is filtered by the year field this row here is filtered by both the year and the quarter and all of these rows are filtered by the year and the quarter and the month so i only want to show a value if i filtered by the quarter level downwards to do that with the average runtime previous quarter measure selected we can add an if function so we can say if i'll just take that calculate function down a couple of lines and then we can use the is filtered function.
I want to check if i filtered by the movie's release date quarter field if that's true i can type in a comma there i want to calculate what i've already calculated and then simply need to close around brackets at the end if we miss out the third parameter of the function we'll receive a blank so this is basically going to get rid of any value for the previous quarter at the year filter level so when i.
Update that measure now we should see we no longer have the average run time for the previous quarter when we're at the year level and that will also remove the comparison value as well we could use the same technique to remove the average time for the previous year from the total row of the matrix again the range of dates included in the original average runtime for the total row or the overall average is every date from the 1st of jan 2010 to the 31st of december 2016. so if we shift that range of dates back.
By one full year in principle that would be the 1st of jan 2009 to the 31st of december 2015 but of course because 2009 doesn't exist in our date column it starts started the first of jan 2010 we only want to display this value if we have filtered by at least the year level of the date hierarchy so using the same basic technique let's find the average runtime previous year measure and then in front of the calculate function we can add an if function.
And then we can check if is filtered and then we can refer to the release date field and the year subfield from the auto date table in there so if that's true we'll perform the calculation we've already performed and then we'll close the extra set of round brackets for the if function and update the measure to get rid of the comparison for the total row so far we've been creating one measure.
To return the value for the previous interval and then a separate measure to compare the previous value with the current interval that's nice and handy in a training video like this one because it makes it easy to see which values we're comparing to get our end results but in the real world it's unlikely you'll want to occupy quite as much space as this in your report to show the value of the previous interval when you can already see that in the original column so i'm just going to tidy up this matrix a bit by getting rid of the average runtime previous year and.
Average runtime previous quarter so we just have the comparison columns displayed i'd like to create a column which compares the average runtime with the previous month i don't want to have to create two separate measures to do that so i'm going to try to do everything all in one single measure we'll build this up step by step and we'll use some variables to help us break the formula down into separate discrete stages so let's start by copying and pasting the average runtime either the previous.
Year or the average runtime previous quarter it doesn't really matter which of those two you copy but that's the basic pattern to use for the starting point so i'm going to copy that and then add a new measure to the all measures table and then paste in all that code i'm going to change the measure name so that it's average runtime versus previous month and then i'm just going to update my check here i only want to show a value for this first stage if i filtered by at.
Least the month level downwards i don't care about showing this result for the quarter and year summary levels so let's change the um the as filtered function to refer to the month parameter of that date table and then we can change the date add interval from quarter to month so at this stage this measure will just return the value for the previous month but only four levels in the matrix at the month level so if i add that new measure into the matrix and then just have a quick look.
