Then we'll look at how you can use the start of year and end of year functions to calculate pretty much exactly what they say they calculate before we look at a couple of more convenient dax.
Functions for working with the year-to-date figures so we'll look at the dates ytd function to get all the dates from the start of the year up to the end of the current interval and then.
The total ytd function to do a similar thing but in a much easier syntax at the end of the video we'll have a quick look at how you can modify the end date of a year for the measures you.
Create using some of the other functions we've already used in the video so plenty to do in this one let's get started to get started i've created a new report.
Video if you'd like to this workbook is a cut down version of the full movies file that we tend to use for this tutorial series it's only got movies ranging from 2010 to 2016..
I've also made sure that before i imported the data i've enabled the automatic date time feature in power bi desktop just as we did in the previous two videos in this section of the.
Tutorial so this guarantees that when we import date data like the release date column from the movies table we get an automatic hidden date calendar table created for us in the background so you.
Can see if i expand the release date field it's got a built-in date hierarchy with access to year quarter month and day already to get started we're going to use that.
Release date date hierarchy to build a matrix which shows the sum of runtime minutes for years quarters and months so we'll start by building a measure to show the sum of runtime so i'm going to.
- How To Add Animated Card To Your Blogger Website (GSAP Animation)
- How to Connect Typeform And Google Sheets With Integromat
- Breaking News! We FINALLY get the Excel functions we've been Wanting!
Right click on the all measures table - ID Card Make
That i've imported choose new measure and then i'll just zoom in quickly on the formula bar so we can see what's going on i'm going to call this one sum.Run time and we'll make this one equal to the result of the sum function referring to the movie's runtime field once i've created that measure we can delete the delete me column so let's.
Right click and do delete from model and then just confirm that we want to do that and then we can insert a basic matrix into the report.
We can assign the release date field to that that's going to insert all four levels of the hierarchy i don't want the day level in there so let's just get rid of that by clicking across there.
I'll just collapse the filters panel and then i'm going to add the summer runtime field to the same matrix so i can check the box next to that as well i'll just increase the size of the.
Matrix on the page and then i'm also going to increase the font size of the values in that matrix using the formatting pane so let's head to the value section and bump up the font size.
To about 13 and then i'll just drill down in the matrix so we can see that the we see the year level the quarter level and the monthly level so we can see the total.
Run time for each different interval now i'd like to create a new measure which calculates the running total for the sum of runtime measure so i want the value of this column to accumulate as we.
Go down the list so that ultimately the value for december 2016 is the same as the grand total for the entire matrix now to get that to work we're going to need to modify the filter context.
Applied to each interval that we've
Displayed in this matrix we know a bit about filter context by now so just as a quick reminder if we were looking at a particular value here.For march 2010 we know that the values included in that measure is everything ranging from the 1st of march 2010 to the 31st of march 2010 says implicit filter context limits the.
Rows involved in this expression we want to modify the filter context so that the interval or the range of dates included still ends on the last date of the current interval.
But begins on the first date of all the dates in the entire data model so just to demonstrate how do you get the first and last dates first of all i'm going to create a measure.
Called last date of interval so let's add a new measure we'll call this last date of interval and then we'll make this one equal to.
The result of the last date function and all we need to do here is refer to the date column in the automatic date table that was created for us so i need to refer to the release date field and.
Then refer to the date column in that hidden date table i can close around brackets and hit enter and then if i add that measure to my matrix we can see unsurprisingly i.
Hope that we get the last date of each interval according to the filter context to do the same thing for the first date is a little trickier if i just copy the measure we've just created there.
And then paste this into a new measure and i'm going to call this one first date of not the entire not the interval but of the entire model there is a first date function as you.
Probably remember from previous videos in this section of the tutorial but if i just said first date of the date field that would be automatically filtered based on the.
Filter context so i'd get the first day of march the first day of february first day of january etc so i'm going to add inside the first date function the all function to.
Effectively remove any filters applied by the context so that will return all of the dates from the date column of that date table and if i hit enter there and then add.
That measure to my matrix you should see that the date for every single row in that matrix now shows exactly the same value the 1st of jan 2010 which is of course the first.
Date of the date column in our data table so now that we've calculated the dates we need we can use those to modify the filter context for our next measure so.
Let's add a new measure to the all measures table i'm going to call this one sum runtime running total and then we're going to use the.
Calculate function to modify the filter context applied to the evaluation of the measure so let's say equals calculate the expression we're going to calculate is the original sum of runtime measure.
But we're going to modify the filter context by using the dates between function you may well remember this from the last couple of videos in the series so we're going to refer to the release.
Date field and the date column of that table and then we're going to refer the start date to our first date of model measure and then the last date to the.
Last date of interval measure we can then close a couple of sets of round brackets and then enter that measure add it to the matrix we don't really.
Need to see the start date and end date or first and last date measures in there just let's tidy this up a little bit and we'll hopefully see that this value now accumulates as we.
Work further through the table so as i scroll downwards the value gets bigger and bigger and eventually the last date or the last month we show has the same value as the grand total.
For the entire matrix now let's say we want to reset this running total when we reach the beginning of a new year so the value for january in the two columns would be the.
Same and the value for december would be the same as the grand total for the entire year to do that we'll need to be able to calculate the start date of each year.
Unfortunately dax has a function for that called start of year so let's just add a brand new measure so we can show this value displayed in a separate column let's call this one.
First date of year and then we can make this one equal to start of year you'll notice there's a couple of associated functions there start of.
Month and start of quarter so if you're trying to accumulate for smaller intervals of time you can use those functions to get the the date you're interested in but for this example we'll.
Go with start of year and then we need to reference our dates column again so that's going to be movie's release date dot date close around brackets and hit enter and.
If i drop this measure into the table we should see that in 2016 the result is the first of january 2016 in 2015 it's the first of jan 2015 etc etc so having done that we can use this new.
Date in a new measure let's copy the sum of runtime running total and then we can paste this into a new measure.
