Today, in Episode #1337, Bill talks about Time Intelligence Functions using PowerPivot and Excel 2010. Following up on Podcast #1327 [DAX Measures: Podcast #1327], Bill makes a few modifications to his file to calculate MTD Revenue.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
PowerPivot for the Data Analyst, chapter 11.
Time intelligence.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
This one is absolutely wild.
So, hopefully we watch the video for chapter 10, where I talked about dax measures and how dax measures in general, with respect all of the filters that apply to a cell, but we can override, certain filters So, 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 in the year of 2008.
So, that's how I'm getting this particular number.
But I'd like to see for example, let's say month to date.
So, I'm going to 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.
All right, and we'll call this month to date sales and we're going to do a calculation again of equal calculate, press [ Tab ] and I want the sum of number at the table started with demo.
So, I type DE and I want demo, revenue.
Close that parentheses to finish off the expression, okay!
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 dates, month to date and then give it the name of the date field.
Okay! 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 parenthesis to close that filter and then a second closing parenthesis 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.
All right, so there's the total for January, it's smart enough.
No, smart enough now that when we get to February, it starts over again with a new month 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...
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.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
This one is absolutely wild.
So, hopefully we watch the video for chapter 10, where I talked about dax measures and how dax measures in general, with respect all of the filters that apply to a cell, but we can override, certain filters So, 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 in the year of 2008.
So, that's how I'm getting this particular number.
But I'd like to see for example, let's say month to date.
So, I'm going to 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.
All right, and we'll call this month to date sales and we're going to do a calculation again of equal calculate, press [ Tab ] and I want the sum of number at the table started with demo.
So, I type DE and I want demo, revenue.
Close that parentheses to finish off the expression, okay!
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 dates, month to date and then give it the name of the date field.
Okay! 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 parenthesis to close that filter and then a second closing parenthesis 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.
All right, so there's the total for January, it's smart enough.
No, smart enough now that when we get to February, it starts over again with a new month 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...
Hey! I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel.
