Solving a business problem with Powerpivot CTP3 using DAX Timefunctions

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.

This is where the problems start, when you put this calculated measure in a Pivot with year on the y-ax (you need a time dimension to use a time intelligent function) the function works like it should (conform the specs), it shows me the sum of the last month in a year. This gives me:
Row LabelsSum of OrderQuantityTotal last month of year
20051013235
20062677330
2007244435270
200832265
As you can see 2005,2006 and 2007 work like i wanted but 2008 is empty, and this is because my date dimension had values until 8/31/2008 while my data has values until 7/31, he sums over month 8 which has no data. This is not what i want, I want the last value of a month that has production values.
_
The problem is in the underlying function of DATESMTD being:
DatesBetween (Date_Column, StartofMonth (LastDate (Date_Column)), LastDate (Date_Column)) (from bol) It uses the lastdate taken from the time dimension.
Putting lastdate in a measure (=LASTDATE(‘DimDate'[FullDateAlternateKey])) you see lastdate result in the following:
Row LabelsSum of OrderQuantityTotal last month of yearlast date of year
2005101323512/31/2005 0:00
2006267733012/31/2006 0:00
200724443527012/31/2007 0:00
2008322658/31/2008 0:00
_
Marius (MSFT) suggested 3 options to solve this problem on the PowerPivotPro post:
1. Do nothing – the results are correct, since there were no sales in the last month of 2008 (August).
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.
Option 1 and 3 are not an option in my scenario because 1) I understand this is correct conform specs but not to my scenario  3) i want to have all data available for other measure
_
Leaves option 2, i tried to implement it using this function:
=LASTNONBLANK(‘DimDate'[FullDateAlternateKey], sum(‘FactInternetSales'[OrderQuantity]) > 0)
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’)))
It appears I misinterpreted the expression part of the function:  The key is the expression needs to return null/blank. It should not be a Boolean expression evaluated to true or false.
My expression is a Boolean expression and will never be null/blank. The countrows will count the rows of the factinternetsales related to the date, when no rows are counted it will return null, LASTNONBLANK returns the last value that returns a count of rows, in my case 7/30/2008 0:00. This was exactly what i was searching for!

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