We can head up to the file menu choose options and settings then choose options and then when the dialog box appears we can head on to the current file section choose the data load page and make sure the auto date time option is checked this guarantees that we get an automatic hidden date or calendar table created for each date field we import into the data model now we've done that we can choose to import some data from excel we're going to use a different workbook compared to the movies file we tend to use for examples in this tutorial so we're going to open up a workbook called wisealt.
Widgets and i'll drop a link as usual in the video description so you can download that file yourself and we're just going to select all the three tables that belong to that worksheet or that workbook so i'll select all three tables and then click the load button now that that's done let's have a quick look at the data we've imported by clicking the data view button on the left hand side and if we start by looking at the share prices table the idea behind this workbook by the way is that yzl's decided to diversify from delivering training courses into selling.
Widgets and gizmos we've got two worksheets the share prices worksheet contains our share price on various dates we're doing quite well by all accounts um so we've got the open price and close price for our shares on a range of different dates and then the stock level is the level of inventory we have for the widgets and gizmos that we're selling and again we've got different values on different dates we'll use that a little bit later on in the example we're going to begin by looking at some stuff to do with the share prices table.
If we head back to the report view i'm going to start by inserting the matrix into the report page and then i'm going to assign the share price date field to that matrix so you can see if i expand the share prices table i've got a share price date you can see that that's used an automatic date table in the background i've got a built-in date hierarchy i definitely didn't create that myself so i can just check the box next to the share price date and add all the levels of the hierarchy to that matrix now i'd like to create a couple of.
Just quickly copy and paste it into another new measure and we'll call this one some close price and then change the field we're referencing here to the close field okay now that we've created those two measures we can delete the delete me measure by right clicking on that eventually right clicking on that and choosing delete from model and then we can click ok to confirm we want to do that and then we can add these two prices or these two measures to our matrix.
Let's just increase the size of this to make it a bit easier to read i'm going to head to the format panel head to the values category and then just increase the font size up to about 13 and that should make things a little easier to read finally i'm just going to expand this matrix out into its various different levels of the date hierarchy using the expand all down one level in the hierarchy button so i'll click that to expand to the quarter then to the month and then to the daily level.
Now the data that we've imported in the share prices table doesn't really lend itself to being displayed correctly when we have summary levels like this in a matrix for month quarter and year if we just imagine for a moment that the values in this column represented say sales that i'd made on an individual day then it would be correct for january to just give the sum for all the sales made in january then for quarter one the sum of all sales made in quarter one etc that's not quite what we're showing here the values on each different day simply.
Represent the value for the same thing at different points in time so the total for january here shouldn't be the sum of everything in january the value that i want to see for the open price for january should just be the same as the open price for the 1st of january so the first date of that particular period so in order to do that we're going to need to recalculate the start date or the range of dates used for each of these summary levels for the open price column we want to refer to the first date involved in that.
Particular interval so the first date of january first day to quarter 1 first date of 2018 for the close price we want the last date involved in that particular interval and if you've been watching previous videos in this section of the uh the series you'll already know about the first date and last date functions let's just quickly include those in a couple of separate measures just to demonstrate what they do if i create a new measure i'm going to call this one first date of interval.
Posts Related:
- Wise Owl Answers - How do I unzip files using VBA
- SQL Server Programming Part 1 - Stored Procedure Basics
- 20 Urban Poster Wall Mockups Free Download Sheri Sk Photoshop Tutorial
And we'll make this one equal to the result of the first date function and all we need to do here is refer to the
Column which contains the continuous range of dates for the table we're looking at as we've been using the automatic datetime feature we can rely on the share price date field so i'm going to start typing that in share price date and then i can refer to the date property or the date column of that built-in date table i can then close around brackets and hit enter and then while we're here why not just.Quickly copy and paste that into another newser and change its name i'm going to call this one last date of interval and indeed we can use the last date function to do that so now that we've done that we can include these two dates in our matrix and we can hopefully see that we get the first and last date of each interval for which these measures are displayed if we.
Just drill back up again you can see that that changes the first date and last date for the quarters and months and for the years as we go further through the matrix so now that we have the first and the last date for each interval we can use those values to modify the filter context for each row of the matrix and get the open price and the close price on those given dates let's enter the first date of interval measure first and i'm just going to modify this one i'm going to change its name to say open price on first date of.
Interval so open price on first date of interval and then i'm going to use the calculate function to modify it so i'm going to add the calculate function open some round brackets and what we're going to calculate is the original sum open price measure but we're going to modify the filter context by applying the first date function as a filter hit enter and that will change the date to showing the actual value similarly we can do the same thing with the last date of interval let's call.
This one close price on last date of interval and we can use the calculate function to do this so calculate and again i'm going to refer to the existing measure sum close price and then we'll apply the last date function as the filter that will apply to the filter context so there's the second measure created and if we just have a look back at the matrix let's just drill down to the individual daily level again so that we can hopefully see that for the.
Individual daily level things haven't changed we're still seeing the same values the first date and the last date of a given date is just that date but for january now rather than showing the grand total for january in the open price we're seeing the same value as the open price on the first row and for the close price we're seeing the same value for the close price column on the last date of january and that's true for every interval here so we've got the open price for quarter.
One the same as the first of jan and then the close price for quarter one should be the same as the last date in march so that would be 31st of march 4978 and we got 4978 there as well now that we have the opening price and the closing price for each interval we can work out the growth for the interval either as a numeric value by subtracting the opening price from the closing price or as a percentage by subtracting the opening price from the closing price and.
Dividing it by the opening price let's just add another couple of quick measures to demonstrate that so i'm going to have a share price growth value and this one's simply going to subtract the opening price from the closing price so we'll say close price on last date of interval minus open price on first date of interval and then we can add that to the matrix then we can add another new measure just to display that as a percentage let's call.
This one share price growth percentage and we'll make this one equal to divide and then we'll refer to the share price growth value and divide that one by the open price on first date of interval when we've created that measure we'll also format that one as a percentage and then we can display that one in the matrix itself okay so there we go i think we should just tidy up a little bit perhaps and get rid of the some of them rising some.
Of close price they're not particularly relevant anymore we've established that they're showing the wrong values for the roll-ups and there we go there's the
Final matrix it might also be nice to display this set of values as a chart rather than as a simple matrix so let's just quickly duplicate page one by right clicking on it and choosing duplicate page then i'm just gonna drill up in the matrix so i can drill up to the yearly level and all i really want to see here is the share price growth value so let's get rid of all of the other fields or measures apart from that one now we've done that we can change the.Matrix into a simple line chart just by clicking on that visual icon in the visualizations panel and there we go we're seeing the share price growth value by year and then we can drill down into the year and quarter year quarter month and even down to the individual daily level as well let's head back to the original matrix and if we just drill back down again to the lowest level we can hopefully see that the growth we're calculating is between the start and the end of each individual interval.
What if we wanted to do something a little more sophisticated say for example we want to compare each individual interval with the value from the start of the quarter for example or the start of the year or the start of the month it doesn't really matter any of those standard intervals we have functions to calculate both the start date and the end date of so i'm going to add a new measure in fact i'm just going to quickly copy and paste an existing measure i'm going to copy the open price on first date of interval measure and having copied it i'm going to create.
Another new measure paste in everything i've copied and call this one open date on first date of quarter or qtr for short so here i'm going to get rid of the first date function and i'm going to replace that with the start of quarter function and you can guess hopefully immediately by get by the name of the function what that will do there are equivalent functions to calculate the start of the month and start of the year and it was the end of the period you were interested in this end of month end of quarter and end of.
Year but for this example i want the start of quarter and as usual i need to reference the continuous range of dates i'm going to refer to my share prices share price date dot date field close around brackets and hit enter to create that new measure so if i just add this one into the matrix what you should be able to see is that for every single date between january february and march of 2018 i'm getting the value for quarter one in.
2018 so that doesn't change until we've reached the end of march and when we begin april it then starts with the value for the start of quarter two if i just drill back up in the matrix so we're not seeing the daily levels it's a little easier to see the change between quarters so now i could compare the close price on the last date of the interval with the opening price for the start of the quarter of our interval belongs to.
So we can use the start and end of functions to calculate the start and end date of any standard interval and pass those into the calculate function to modify the filter context but dax also provides a range of functions to make that syntax a little easier it's not particularly complicated as it is but it could be even easier i'm going to select the close price on last date of interval measure i'm just going to copy all that code to the clipboard and then create a new measure and i'm going to calculate the close price on the last date of the year.
So one way we could do that is to get rid of the last date function and replace that with end of year so we've just seen the start of quarter is the equivalent for the end of the year so it's not particularly complicated we can enter that to create the measure and then just drop that into the matrix and i just need to change the width there to show that it is indeed returning the value for the last date of the year so the closing balance for 2018 is displayed next to every quarter and month and indeed today for 2018. when we.
Get to 2020 or 2019 a little bit too fast sorry the close price for 2019 we're seeing that next to every interval there now although that syntax wasn't particularly complicated it could be slightly easier again let's just change the calculate function entirely i'm going to use the closing balance year function so you can probably guess what that one does there are a few extra parameters here to make it a little more flexible than the uh from the functions we've used so far but what i'm going to do is calculate.
The close or some of close price measure and the range of dates i'm going to use once again is going to be the share price date dot date field now i could apply an extra filter but that's an optional parameter and i can also control the date on which the year ends if it isn't the 31st of december but i just want to get the exact same results i've already received here so that very basic pattern i get the exact same end results so for 2018 i get the value for.
The closing price at the end of 2018 and that changes when we reach the next year as well as a range of closing balance functions there's also a set of opening balance functions for year quarter and month but you do have to be slightly careful about how you apply those depending on the arrangement of data in your data model i'm just gonna make a quick copy of the closing price for the last date of year using the closing balance year function.
I'm just going to copy that create a new measure and then paste all the code into it i'm going to get the open price on the first date of the month i probably should have just written this from scratch i suppose first day of month and then i'm going to change the function i'm using here to the opening balance month function again there's quarter and year variations as well i'm going to change this so i'm getting the open price rather than the close.
Price and then update that measure and add it into the matrix i think we should tidy up a little bit here though let's get rid of the the the growth column so growth value and growth percentage and then replace that with open price on first date of month now you may see a slightly different result than you initially expect here i don't know if you can make out here i've seen the value no value for january quarter 1 or 2018 the first value i.
Receive is for february the value that i'm seeing that doesn't appear to match the start date or the opening price of any months that i can currently see if i just drill down into the next level it makes things a little clearer i think if i scroll down to the first date of february i get the value 7141.59 if we go back by one single day to the 31st of jan we see that's the same value so the opening balance functions don't actually give you the value for the opening balance of the current period.
They give you the same value as the last date in the previous month or quarter or year so this one's going back one day from the start of the month to give me the closing balance if that doesn't seem to make sense it makes an awful lot more sense when you don't have separate columns for open price and close price often you just have a list of balance values in a single column and the opening balance of one period is the same as the closing balance of the previous one.
So that's why the opening balance functions work like that just be slightly careful about applying them depending on how your data is arranged retrieving values for individual dates from the share prices table has been quite simple and the main reason for that is that in the share prices table we're recording a value for both the open and the close price on each individual date between the beginning and the end of the data model so no matter whether we're trying to get a value for the start or the end of a year quarter or month we always know there's a value there to be retrieved.
That's not quite the same in the stock level table if we select that in the data view we can see that the stock level table is recording the quantity of stock we hold for two different products on different dates and that's based on a stock check so the stock checks take place at different intervals for the widgets column we're taking a stock check every week starting on the 1st of jan 2018 and then every 7 days thereafter for gizmos we're taking a stock check twice a month once on the 15th of the.
Month and then once again on the last date of the month so you can see there's a bit of a mismatch between the two different columns sometimes the stock check dates coincide for both widgets and gizmos sometimes there's only one or the other not every single date is represented so you can see there are quite big gaps in this list so how does that work when we try to retrieve things for the start or end of different intervals in a measure let's head back to the report view and i'm going to create a new report page and in here i'm just going to add a.
Basic matrix again to show the dates for our stock level table so let's expand the stock level table then let's add the stock taking date to the matrix then i'd like a measure that shows me the sum of gizmos let's start with a monthly stock check products so we'll have a new measure i'm going to call this one sum gizmos and then we'll say equals sum and then refer to the gizmos column of.
The stock level table we can then add that one into the matrix let's just do a little bit of basic formatting to try to make this a little more readable so we'll just do the usual thing and bump up the font size and then increase the size of that matrix and then we can drill down into it so we can see down to the daily level what values we're returning just as with the share prices example we have the same problem with the summary rows in the matrix where the values for.
The month quarter and year are showing the totals for all the stock level values within the respective intervals what we really want of course is the value for a single date within each interval now if that date happens to be the last date of the interval that's nice and easy because we've got a stock check for the gizmos product happening on the end date of each month which also means we have the end date of a quarter and of course the end date of a year so we could just use our last date function to get the stock level on the.
Last date of each interval let's just do that one quickly let's have a new measure in the all measures table and i'm going to call this one gizmos quantity or something along those lines this gives them as qty on last date of interval and to do this we can just use the calculate function we can refer to our sum of gizmos measure and then we can refer to the last date function and then refer to the stock taking date.
So don't forget we're in a different table now so we want to refer to the calendar table associated with the stock taking date column so we can refer to the date column of that built-in automatic date table close around brackets for the calculate function and then we can add this new measure into the matrix and see that we get the value of stock for the last date of each interval shown next to every individual row in the matrix so at this point we.
Could get rid of the original sum of gizmo's measure we don't really need that one anymore it's not showing us the right value anyway and that's what we end up with because we have a value for the last date of each month we could also use the end of month quarter or year functions or the closing balance month quarter or year let's just quickly demonstrate that by copying the gizmos quantity on last date of interval measure and then we can create a new measure.
And we'll use this one to show the quantity of gizmos on the last date of the month so let's just modify the name there to change interval to month or mth and then we can change the last date function to say end of month if we just update that measure now i'm just going to drill up in the matrix one level so we can just see things at the monthly level to begin with and then i'm going to add that new measure into the matrix just so you can see that when we're looking at things from the monthly.
Level upwards we get the same results if i just drill into any individual month however the results here are a little different hopefully you can see the big difference is that the end of month function calculates the end of the month for every single date in the month so it's not just the dates which have values for the gizmos quantity or the stock checks for gizmos we get a value for every single date in the month.
What you can hopefully see is the stock take on the 15th although we can see the original stock take value in the last date of interval so the last date of the interval for the 15th of jan is of course the 15th of jan the last date of the month for that interval is the 31st of jan so that's why we're seeing the value 218 next to every single row for that month we'd see the same effect here if we use the closing balance function instead if we just head back to the gizmos quantity on last date of month we can modify this entire measure so we're not using the.
Calculate function at all we can use the closing balance month function so for the expression we'll refer to the sum of gizmos measure that we created earlier on and then use the dates column for the stock taking date so stock level stock taking date dot date so we'll get the same end result here just using that slightly simpler syntax of the closing balance month function so because we have a value for the last.
Date of each month we could use the end of month or closing balance month functions to get the same value we don't have quite the same flexibility if we want to get the quantity of gizmos held at the start of each month the main problem here being the fact that we don't record the stock level at the first date of each month so we try to use the start of month function let's just do this even though it's not going to work we'll copy the gizmos quantity on last date of interval measure again and then create another new measure and paste in all that code.
