Particular property set related to dates and times so in the current file section in the data load page i'm going to head over here and make sure i've got the auto date time option checked all the time intelligence functions we're going to use in this video rely on all of our date columns in our data model having a calendar table or a date table set up so rather than go to the effort of creating our own we're going to rely on the built-in automatic calendar table that power bi can create for us.
We will have a video on how to create your own calendar tables later in this series but for now we'll stick with the default automatic one so making sure that box is checked we can click ok and then we can choose to import data from our excel workbook as usual i'll drop a link in the video description so you can download this file yourself it's a cut down version of the standard movies workbook we tend to use so it's only got movies from 2005 to 2016. i'm going to double click to open up that file then there are two tables or two.
Worksheets in here one is a blank all measures table which will just save us a little tiny bit of time in creating a measures table in a moment and then we've got the data table called movies so it's got all the data contained within there as you can see if we click the load button all of that will be loaded into the basic data model okay so now that that's done i'd like to start by creating a basic measure to show the total run time for our films so i'm going to right click on the all measures table and choose new measure and then i'll just quickly zoom in on.
The formula bar so we can see what's going on and i'm going to call this first measure sum run time and i'm going to make it equal to the result of the sum function referring to the runtime column in the movies table so there's our basic measure having created that one i can delete the delete me column just to tidy up this table so let's delete that from the model and click ok to confirm we want to do that and then i'd like to display the value of that measure in a table next to the year that the films were released in so in the report i'm going to insert a.
The table selected head to the format pane and then choose values and bump up the font size to about 13 just to make things a little easier to read and there we go there's our first basic table and measure in the previous video in this series we looked at a couple of basic time intelligence functions which allowed us to manipulate the filter context applied when you display different intervals of time in a visual so in this table each row has a filter context which limits.
The data contained in that row to only films released in that specific year just as a quick reminder of something we did in the previous video i'd like a new measure that allows us to display next to each year the sum of the runtime for the previous year we have a couple of ways we can do that let's get started by creating a new measure in the all measures table and then i'll call this one some runtime previous year or an abbreviation of that at least and we're going to use the calculate function to modify the filter.
Context so the expression that i'm going to calculate i'm just going to reference the existing measure some runtime we could of course nest the sum function in here again but as we've already gone to the effects of creating the measure we might as well use it and then i can apply a filter using a couple of different time intelligence functions so one fairly simple function we could use would be same period last year in order to make this one work all we have to do is refer to the dates column created in the automatic date table.
So i'm going to refer to the release date field and then reference the date property of that field the date property coming from the date table and then going to close the round brackets and then close an extra set of round brackets for the calculate function then i can enter that formula and i can add that new measure to my table and we can see that the value for the preceding year is displayed next to each year in the table so the same period last year function is quite a specific one it always takes the range of dates included in the filter.
Context and shifts it back by exactly one year you'll often want a bit more flexibility than that let's show how to create the same end result using a more flexible function i'm going to head back to the sum runtime previous year measure i'm just going to make a copy of this one then we can create a new measure paste in all the code we just copied change the name to an inventive name like some runtime previous year 2 and then we can take away the same period last year function and replace that with a more sort of general purpose.
- Excel VSTACK - Quickly Combine Data from Multiple Sheets (Excel Noob Vs Pro)
- Ficha de Membro - Planilha Cadastro de Membros e Controle de DÃzimos.
- Unir Planilha Login Excel com Planilha sem Macros - Parte 03
Time intelligence function called date add now the data function - ID Card Make
Provides three parameters and the first one is again the range of continuous dates in your date table so i'm going to refer to the release date field and the date property of that then the next parameter lets you specify how many intervals you want to move forwards or backwards so if i want to go back one year i can say -1 but if i want to move forward in time i can use positive numbers instead then finally i can specify what interval.I want to move so it's not just about moving in years i can move in days months or quarter intervals as well but as here i'm trying to replicate the same example we've just created with the same period last year let's use the year interval close a set of round brackets for the date add function and then create that measure i can then add that new measure into my table we can see what the names of them are and then we can see we get exactly the same result using a slightly different.
Function providing a bit more flexibility in our date ranges dax has a large number of time intelligence functions which behave in very similar ways and that often means that you can achieve the same end result using lots of different techniques just to demonstrate that again i'm going to introduce a new time intelligence function called previous year so i'm going to head back to the sum runtime previous year measure and then we're going to copy that we're going to create a brand new.
Measure giving it pasting in the other code and giving it an equally inventive name some runtime previous year 3 and then we're going to take away the same period last year function and then use the previous year function instead you'll notice that there's also a previous quarter month and day function for different intervals of time and if i wasn't trying to go backwards in time i was trying to go forwards there are equivalent functions called next day month quarter and year as well this one though i'm trying to replicate.
The same example so i'm going to go for previous year open some round brackets there's one compulsory and one optional parameter so dates again being the continuous range of dates in your date table and an optional year end date we'll just stick with the same examples we'll just refer to the release date and then the date property of that to get the same end results create that new measure and then we can add that one into the table and we can see once again the same results as previously one subtle difference is the lack of a grand total.
At the bottom but the important thing is that for each year we can see the sum of runtime for the previous year now the names of some of the dax time intelligence functions can be a little misleading taking for example the previous year function we've just used here if you see it in an example like this you'd be forgiven for thinking that the previous year function behaves in an almost identical way to the same period last year function or the date add minus.
One year but that's not quite true we can expose the quite different behavior of the previous year function by including extra intervals in our visual i'm just going to change the visual from a table to a matrix and then i'm going to include the quarter and month intervals in that matrix so at the moment we're still seeing the same results let me just change the size of this visual again.
And what i'm going to do now is i'm going to drill down so that we can see not just the year levels but also the quarter levels so i'm going to click this split arrow to expand all down one level in the hierarchy having done that you can see that the result of the previous year function is quite dramatically different to that of the same period last year and the date at minus one year if i drill down to the next level again so i can see the individual months as well as the quarters and years again you can see that the behavior of that same.
I'm sorry the previous year function is quite different so no matter what interval of time we're looking at for the previous year function it's always returning the total for the entire previous year previous year takes the last date for any particular filter context so for example if i pick may 2006 the last date in that filter context is the 31st of may 2006..
That date is shifted back to the previous year so we get the 31st of may
2005 and then the range is expanded to include the entire year so the range of dates for any interval displayed in 2006 is always the entire year for 2005. if we move into 2007 2007 every single interval displays the grand total for 2006 and so on and so on and so on.You'll see that same behavior with all of the previous or next interval functions just to demonstrate that let's have a look at comparing quarters i'm going to first of all head back to the sum runtime previous year 2 function the one that uses the date add function and i'm going to copy that one and then create a new measure paste on all the code that i've just copied and then change its name to be some runtime a previous quarter or at least an abbreviation of that all i'm going to do here is change the interval i'm using in the date add.
Function from year to quarter and then i can create that new measure then i'm going to head back to the sum runtime of previous year 3 function that uses the previous year function copy that measure and then i'm going to add a new measure paste in all the code i've just copied give this an inventive name i'll call this one some runtime previous quarter 2 and then this time we'll change the name of the function we're using from previous year to previous quarter.
The same parameter is required so it's the continuous range of dates so i can create that second measure then i can add both of these two new measures into my matrix let's go with some runtime previous quarter some runtime previous quarter two and see what difference is made it's probably easier to see if i just drill back up one level so that we're looking at just the year and quarter levels at this point most things look pretty similar so the summary for in each.
Individual quarter is the same so if we're going back one quarter we get the value for the previous quarter there's discrepancies in the totals there of course which kind of indicate that there is some different behavior going on but if we were just looking at the quarterly level then things appear identical drilling back down to the monthly level again though you'll notice quite a bit difference again so whereas the value for each individual quarter.
In the date add function is still returning the total 4 that same period shifted back by one quarter the previous quarter function is showing the total for the entire previous quarter next to each interval now the point of describing all this isn't just to bore you or put you off watching any more of my videos the point is just to make you aware that you should be careful about assuming the behavior of a function judging only by.
Its name and i think that in turn leads you to be a little more careful about the way you name your measures as well if i just tidy up the matrix a little bit i'm going to get rid of everything except for the original sum of runtime and then the sum runtimes previous years two and three if i just drill back up again so we can see just the yearly level the fact that these two measures have almost identical names some runtime previous year i think that does fairly accurately describe what those measures are showing but as soon as we start drilling down.
The different results displayed here should make you think a little more carefully i think about what names to use i think perhaps a better name for this some runtime previous year 2 would be perhaps some runtime same period previous year so let's just update that same period or same interval much like the same period last year function quite accurately describes what it does we could then rename the sum runtime.
Previous year 3 to say something like some runtime entire previous year or maybe total runtime previous year but something which more accurately indicates what it is returning so what might be the point of returning the total run time for the entire previous year next to every quarter a month interval of the current year well one simple reason might be just to calculate the percentage contribution of each interval to the total for the previous year so to do that let's just quickly tidy up.
This matrix again i want to show just the sum of runtime for the current interval and then the sum of runtime for the entire previous year if i take this value and divide it by this value we'll get the percentage contribution to the previous year's total so to make that work let's add a new measure i'm going to call this one sum runtime percentage of previous year total i make this one equal to i think the actual expression is shorter.
Than the name of the measure this time so we're going to use the divide function and the numerator will be the sum of runtime measure and the denominator will be the sum of runtime for the entire previous year i think this demonstrates the importance of having sensible measure name so you know exactly what you're getting okay so having done that maybe i'll just quickly tidy this layout up a little bit as well just to make it easier to read okay and then once we've done that we.
Can enter this measure i'm just going to change the formatting as well so it's formatted as a percentage and then i'm going to add that one into the matrix and we ought to be able to see at this point how each quarter of the current year contributed to the grand total for the previous year also how the grand total for the year compares to the grand total of the previous year so in 2006 it looks like we released more minutes of movies and we drill down into the months we can see that same.
Basic statistic broken down by smaller intervals at the start of the video we looked at two different ways to go back to the same interval in the previous year so we saw there was the same period last year function which always goes back by one year we also saw that we could use the date add function specifying this time that we want to go back minus one year but with the flexibility to add just the number of intervals and the intervals.
Themselves we've also seen how to go back one year and get the entire previous year so we looked at using the previous year function and the previous year function much like the same period last year function always goes back one year with previous year we've also got next year we've got previous quarter and next quarter so we do have a bit more flexibility to go forwards and backwards by one interval but what if we want even.
More flexibility than that is there an equivalent function to the date add function but to get the entire period so go back one year or any number of years and get the entire year well there is and the function is called parallel period just to quickly demonstrate this i'm going to replicate what we've done with the sum of runtime for the entire previous year using the parallel period function.
The measure i'm going to copy though is the one that uses the date add function because the basic syntax is pretty much the same so i'm going to copy the some runtime same period previous year and then i'm going to create a new measure and then i'm going to say some runtime entire previous year 2 because we've already got a measure called that one i really need better naming conventions for my measures and then we're going to change the date add function to the parallel period.
Function now just to show you that the range of parameters for parallel period you need to start by referencing the range of continuous dates so that's moviesreleasedate.date then it's the number of intervals and then what specific interval you want to use so we're going to go back one year just to quickly mention that there's a slightly different list of intervals you can go with parallel period so it's only month quarter and year you can't use days with this function anyway we're going to go back one year.
And we'll get the entire range of dates for that interval so in this case we'll get the entire year so i'm going to create this new measure and then i'm going to place this one side by side in this matrix with the sum of runtime entire previous year just to show that we get the exact same results using parallel period as we do with the previous year function but of course with this added flexibility of going forwards and backwards any number of increments of.
Years quarters or months one useful thing we could do here with the parallel period function is get the total runtime for the same year that the interval belongs to so while calculating the percentage of the previous year total is kind of interesting i suppose calculating the percentage of the same year total might actually be more useful so let's just update this some runtime entire previous year two measure i'm.
Going to call this one some runtime entire same year and we can get rid of the action number two at the end so basically i want to shift forwards or backwards zero intervals and i want to return the results for the entire year so if i update that measure now we'll see that next to every individual interval we get the grand total for the year that that interval belongs to.
Then what we could do fairly simply is make a copy of this sum runtime percentage of previous year total measure and then paste that into a new measure and then change its name so it's of same year total and then refer to the sum runtime entire same year okay so having done that we can create that new measure format that as a percentage and then drop that into the matrix.
And there we go so we've seen several techniques for moving forwards and backwards by set intervals but what if you want to specify an exact range of dates for your measures for example maybe you want to count the number of parties that happened at number 10 downing street during the kobe lockdown period or maybe slightly less controversial and perhaps more movie-related note maybe we want to work out the sum of runtime during the writer's guild of america strike between the 5th of november 2007.
And the 12th of feb 2008. i'm not expecting anything interesting to turn up in terms of the statistics here by the way this is purely to show you how to enter specific dates in a measure so let's head back to the power bi desktop app and then we can add a new measure to the measures table i'm going to call this one some runtime during strike and we'll make this one equal to the calculate function and we're going to refer to the sum of runtime measure and then we're going to apply a filter.
To the date range for each interval using the dates between function so the dates between function has three parameters the first one being once again the continuous range of dates contained in your date table so that's movies release date dot date and then we need to specify the start date and the end date now if you've worked with other microsoft products you'll probably be aware of a range of different ways of typing explicit dates into expressions and programming languages.
One thing that you don't do if i wanted to enter the start date here of 5th of november 2007 if you don't just start typing your date directly in now apologies to anyone watching in the us i'm in the uk and i've got uk regional settings so if you bear with me while i enter my date as five forward slash 11 5th of november 2007 that isn't going to be treated as a date at all that's going to be treated as an expression 5 divided by 11 divided by 2007 so one fairly common way to.
Try to enter your date or try to get your programming language to interpret what you've written as a date is to try to enter it as a string as text so if i wrap some double quotes around that that will send text to the dates between function and then it will attempt to interpret that string of text as a date so it's quite important of course that you enter something here that is actually a date if i tried to enter the value boris for example that's clearly not going to work um so i want to make sure that that's something which could.
Legitimately be interpreted as a date then you've got the question of ambiguity so i just mentioned the fact that i'm in the uk five forward slash 11 is the way that i write my dates for 5th of november that's not may the 11th but for somebody else reading this that might not be obvious and if i was sending this to you to different people running this report in different regions of the world that's potentially ambiguous so there are various formats you can write your dates in i could change this to say 5 north or 5 november 2011 that's.
Completely unambiguous and dax will happily interpret that as a valid date as well when you're entering text and expecting it to be interpreted as a date you can often get away with this in dax dax does a reasonably good job of interpreting strings of dates but there's also actually a dedicated function designed to do exactly this so the function is called date value and the idea of this is it converts a piece of text into a valid date so if i say date value and then pass in the 5th of.
November 2007 as a string that's probably the most reliable certainly the recommended way to enter dates as strings just to enter the end date i'm going to take a slightly different approach here i'm going to use a function called date now the idea of the dates function is that you can specify the different units or the different intervals as numbers so if i want to specify the 12th of february 2008 i can enter the number 2008.
