Use the DAX time intelligence functions to create MTD Revenue in a PowerPivot Pivot Table.
This video is designed to accompany the book, PowerPivot for the Data Analyst: Microsoft Excel 2010
This video is designed to accompany the book, PowerPivot for the Data Analyst: Microsoft Excel 2010
Transcript of the video:
MrExcel podcast is sponsored by “Easy-XL”!
PowerPivot for the Data Analyst chapter 11 - Time Intelligence!
Well, hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
This one is absolutely wild, so hopefully you watched the video for chapter 10, where I talked about DAX measures, and how DAX measures, in general, will respect all of the filters that apply to a cell.
But we can override certain filters, and we're going to do that here with time intelligence.
So I have daily sales going down for January 1st, January 2nd, it's respecting this filter, Beachwood Place Mall, region Ohio, all divisions, maybe I can choose just a few divisions here, and the year of 2008.
So that's how I'm getting in this particular number, but I'd like to see, for example, let's say month to date, someone add a brand new measure.
Now to add a new measure, before we went to the PowerPivot tab and chose New Measure, you can also right-click on the table name, and say Add New Measure, alright.
And we'll call this “Month To Date Sales”, and we're going to do a calculation again of =CALCULATE, press Tab.
And I want the SUM of, remember the table started with “demo”, so I type “de” and I want demo[ Revenue ], close that parentheses to finish off the expression.
OK now, the filters can override the filters that are applied to that current cell.
I don't want to touch the filters for region, store name, division, or year, but I do want to change the filter for date.
So here I'm going to say DATESMTD, and then give it the name of the date field, OK.
Well the date field is called date, but of course it's demo date, so I start to type “de” again and then find demo[ Date ], click.
The closing parentheses to close that filter, and then a second closing parentheses to close the whole thing again.
We'll check the formula, the tooltip overrides the results, so click Check Formula again, no errors in formula, click OK, and we'll let that calculate.
Alright so check this out, let's just choose a few different cells here, 1491, and we'll see that the total of those is 7872, which in fact is 7872.
The beautiful thing about this is, as we scroll down, let's go down towards the end of January.
Alright, so there's the total for January, it's smart enough now, that when we get to February, it starts over again with a new month to date calculation.
There's all sorts of time intelligence functions like quarter to date, dates in this quarter, so you can see the percentage of this quarter dates in this month.
Dates in this year, dates from one year ago, even dates from 52 weeks ago, in case you want to compare Friday to Friday.
So the book in Chapter 11 goes through a whole bunch of different calculations, so using those time intelligence functions to make the CALCULATE function even more powerful!
Hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
PowerPivot for the Data Analyst chapter 11 - Time Intelligence!
Well, hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
This one is absolutely wild, so hopefully you watched the video for chapter 10, where I talked about DAX measures, and how DAX measures, in general, will respect all of the filters that apply to a cell.
But we can override certain filters, and we're going to do that here with time intelligence.
So I have daily sales going down for January 1st, January 2nd, it's respecting this filter, Beachwood Place Mall, region Ohio, all divisions, maybe I can choose just a few divisions here, and the year of 2008.
So that's how I'm getting in this particular number, but I'd like to see, for example, let's say month to date, someone add a brand new measure.
Now to add a new measure, before we went to the PowerPivot tab and chose New Measure, you can also right-click on the table name, and say Add New Measure, alright.
And we'll call this “Month To Date Sales”, and we're going to do a calculation again of =CALCULATE, press Tab.
And I want the SUM of, remember the table started with “demo”, so I type “de” and I want demo[ Revenue ], close that parentheses to finish off the expression.
OK now, the filters can override the filters that are applied to that current cell.
I don't want to touch the filters for region, store name, division, or year, but I do want to change the filter for date.
So here I'm going to say DATESMTD, and then give it the name of the date field, OK.
Well the date field is called date, but of course it's demo date, so I start to type “de” again and then find demo[ Date ], click.
The closing parentheses to close that filter, and then a second closing parentheses to close the whole thing again.
We'll check the formula, the tooltip overrides the results, so click Check Formula again, no errors in formula, click OK, and we'll let that calculate.
Alright so check this out, let's just choose a few different cells here, 1491, and we'll see that the total of those is 7872, which in fact is 7872.
The beautiful thing about this is, as we scroll down, let's go down towards the end of January.
Alright, so there's the total for January, it's smart enough now, that when we get to February, it starts over again with a new month to date calculation.
There's all sorts of time intelligence functions like quarter to date, dates in this quarter, so you can see the percentage of this quarter dates in this month.
Dates in this year, dates from one year ago, even dates from 52 weeks ago, in case you want to compare Friday to Friday.
So the book in Chapter 11 goes through a whole bunch of different calculations, so using those time intelligence functions to make the CALCULATE function even more powerful!
Hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
