This is part 3 in my series to solve a much occurring, simple, business problem with PowerPivot,
Part 1: Working with Gemini and Excel 2010 to make a pivot table
Part2: Trying to solve a business problem with Powerpivot, Gemini CTP2
In the previous blog post I tried to create an analysis on the adventureworks dwh where I want to see the OrderQuantity of the current month compared to the monthly average of OrderQuantity last year by promotion. This post will describe my quest for a solution and findings i have on the way.
In CTP 3 we have lot of new things, we have loads of new DAX functions available especially the time intelligent functions i am very enthusiastic about (wish i had them in MDX). In the previous blog post i decided to create the solution using dynamic sets but since the time intelligent functions are available i decided to go and use them, thanks to Rob at his PowerPivotPro’s DAX post who gave me the idea.
Ok we start with the most interesting formula, i want the orderquantity of the current month. The current month will be determined by the the sum of the last month having data, this has brought me all kind of problems, i started with the following formula:
| =CALCULATE( | use calculate to get a value that you want filtered |
| sum(‘FactInternetSales'[OrderQuantity]), | Get the sum of OrderQuantity |
| DATESMTD(‘DimDate'[FullDateAlternateKey]), | Select the last month of the year |
| ALL(‘DimDate’)) | use the all in CTP3 |
Howie (MSFT) explained the reason we need to use the ALL(‘DimDate’) in the the PowerPivotPro thread: When using Time intelligence functions like LASTDATE or DATESMTD, in CTP3, it is necessary to add another argument which is ALL (TimeTable). This won’t be needed post CTP3. Without this, only the selected dates are considered, and you can’t find the last month unless you are in the last month.
| Row Labels | Sum of OrderQuantity | Total last month of year |
| 2005 | 1013 | 235 |
| 2006 | 2677 | 330 |
| 2007 | 24443 | 5270 |
| 2008 | 32265 |
| Row Labels | Sum of OrderQuantity | Total last month of year | last date of year |
| 2005 | 1013 | 235 | 12/31/2005 0:00 |
| 2006 | 2677 | 330 | 12/31/2006 0:00 |
| 2007 | 24443 | 5270 | 12/31/2007 0:00 |
| 2008 | 32265 | 8/31/2008 0:00 |
2. Use LastNotBlank() instead of LastDate(), if that’s the actual intention of the calculation.
3. Remove/filter out the August 2008 rows from DimDate when importing that table, if those rows weren’t supposed to be there in the first place.
This got me exactly the same result as using lastdate, I posted this problem on the MS forums where a solution was found in:
=LASTNONBLANK(‘DimDate'[FullDateAlternateKey], countrows(RelatedTable(‘FactInternetSales’)))
This creates the following function to determine the sum of last month of a year:
| =CALCULATE(sum(‘FactInternetSales'[OrderQuantity]), | Get the sum of orderquantity only from the selection from calculate |
| DATESMTD( | Create a selection of month to end of year |
| LASTNONBLANK(‘DimDate'[FullDateAlternateKey], countrows(RelatedTable(‘FactInternetSales’)))) | Use the last day having data to determine the last month |
| ,ALL(‘DimDate’)) | Use all in CTP3 |
This gives me data in 2008, the idea is when you publish this to SharePoint this function will make sure you always have the latest month.
What i would like to see in PowerPivot is to create a formula to use in the filter, to select the current year. You now have to do that once a year by hand.
Ok we now have the orderquantity of the current month, next is the average orderquantity per month of the previous year. I created this formula to do this:
| =CALCULATE(sum(‘FactInternetSales'[OrderQuantity]), | Get the sum of orderquantity only from the selection from calculate |
| DATESYTD( | create a selection of dates of the year to end of year |
| PREVIOUSYEAR(‘DimDate'[FullDateAlternateKey])) | Select the previous year |
| ,ALL(‘DimDate’)) | Use the All in CTP3 |
This gives me the total sum of orderquantity from the previous year, to get the monthly value I divide the measure by 12:
=’FactInternetSales'[Total YTD last year] / 12 Divide my measure by 12
To make things fun I created a Trend by year measure which subtracts the two measures:
=’FactInternetSales'[Total last month of year] – ‘FactInternetSales'[AVG YTD last year]
And gave it a nice color by using Conditional formatting:
I used number formatting to format the numbers with zero digits and thousand seperator (use right mouse button, value field settings, Number format).
I want to see what the trend of current year is, so i want to compare the last month to the month before last month, we use PREVIOUSMONTH here:
=CALCULATE(sum(‘FactInternetSales'[OrderQuantity]), DATESMTD( PREVIOUSMONTH(LASTNONBLANK(‘DimDate'[FullDateAlternateKey], countrows(RelatedTable(‘FactInternetSales’))))), ALL(‘DimDate’))
And i want to compare the last month of the year with the same month last year, we use PREVIOUSYEAR to get the same month last year:
=CALCULATE(sum(‘FactInternetSales'[OrderQuantity]),DATESMTD(PREVIOUSYEAR((LASTNONBLANK(‘DimDate'[FullDateAlternateKey], countrows(RelatedTable(‘FactInternetSales’)))))),ALL(‘DimDate’))
Together this results in the following PowerPivot App, to smoothen up your PowerPivot app check this blog post: Make your PowerPivot sheet look great in SharePoint 2010.:
Final thoughts: PowerPivot DAX is a very powerful language, i wish i could use this in Reporting services on top of cubes, doing these same things in MDX wil most definitely give us the feared too many rows error. The downside is that it still requires a lot of experience or some great books on DAX functions, like there are on Excel functions, for BI powerusers to be comfortable with DAX. This will maybe be a opportunity for us BI pro’s to help clients with PowerPivot.
If you want to play around with the PowerPivot app yourself you can download it here.
Wow! Thanks for this awesome tutorial! Keep up the fantastic posts and we’d love to hear from you at http://www.facebook.com/office
Cheers,
Andy
MSFT Office Outreach