PowerPivot Use Case: Getting the active products between a date range using DAX

I have been getting this question a few times in the last week, e.g. in my ask a questions page here, on how we can get the number of active products between two time intervals in Excel. Lets answer the question on my ask a questions page in todays blog post.

The question was:

DESIRED OUTPUT: Pivot table showing count of asset available by Month; Column Headings = Months & Year; Row Headings = Class.

TABLE1: Includes a list of assets and corresponding Class, Acquired date, and Disposed date.

Asset,Class,Acquired,Disposed
A,TEL,2/16/2010,10/06/2011
B,LBH,3/20/2010,05/23/2012
C,RBH,4/11/2010,06/22/2011
D,TRJ,1/17/2011,09/05/2012
E,DGH,5/9/2011,05/02/2012
F,DGH,7/18/2011,03/13/2012
G,RBH,9/23/2011,01/21/2012
H,TRJ,5/28/2012,06/07/2012
I,THG,7/2/2012,10/11/2012
J,TBD,8/6/2012,12/02/2012

TABLE2: Date Table including fields for Date, Month, and Year

EXAMPLE OUTPUT: Based on the data above, the pivot table output for June-August of 2011 would be as follows…

Year: 2011
Month: Jun, Jul, Aug
DGH, 1.0, 2.0, 2.0
LBH, 1.0, 1.0, 1.0
RBH, 1.0, 0.0, 0.0
TBD, 0.0, 0.0, 0.0
TEL, 1.0, 1.0, 1.0
THG, 0.0, 0.0, 0.0
TRJ, 1.0, 1.0, 1.0

Ok lets solve this using DAX.

First I load the table into Excel 2013, make a table out of it and push it to the Excel data model.

Observe that I changed the dates to have all dates be in 2011 to make it easier for us to debug.

Next I created a date table in Excel:

Back in Excel I create the pivottable like requested in the question:

First things first, I would like to make sure to sort the months in its natural order. We can do that using the PowerPivot add-in. I created a calculated column that determines the month number:

=MONTH([Date])

I then hide the column and use the sort by other column button to tell the Data model to sort Month by Monthnumber

This results in the months sorted “right” in the pivottable:

Now back to the real problem. Notice that I have NOT created a relationship between the two tables.

First thing that I want to do is create a measure that counts the number of facts

Nr facts:=COUNTROWS(Facts)

In the result below this does the dates are not taken into account, but its takes care of counting the facts for the relationships that are there, like nr of products per class:

We are going to use DAX for that as we don’t have a relationship type that allows us to do a “between” function. The DAX function I created will look at each row from the fact table for each product where the product is active in the current month (acquired before or in this month and disposed after this month) based on the current row and column in Pivottable.

The DAX I wrote to do is the following:

Active Facts:=CALCULATE([Nr facts],
FILTER(Facts,
Facts[Acquired] <= MAX(Dates[Date])
&& Facts[Disposed] >= MIN(Dates[Date])

))

What does this function do ? First it will execute this function in each and every cell of this pivottable, lets take the first cell:

The values in this cell will be filtered to Fact[class] = “DGH” and Date[Month] =”Jan” based on the values on rows and column for this cell, the “Fact” table that will thus be filtered by Fact[class]:

Hence you can see the result of the measure “Nr facts:=COUNTROWS(Facts)” to be 2 in our pivottable image a few images up.

The date table is filtered by the Date[Month] column that is on rows:

That does not change the value of the measure in our pivottable as there are no relationships between “Date” and “Fact”

Now to make sure that we can also filter the Fact table by the dates we extend our DAX formula to do the following: Calculate the “NR Facts measure” where the “Fact” table (already automatically filtered by Class)  has Facts[Acquired] before the current selected Dates[Date] or Facts[Disposed] after the current selected Dates[Date]:

Active Facts:=CALCULATE([Nr facts],
FILTER(Facts,
Facts[Acquired] <= MAX(Dates[Date])
&& Facts[Disposed] >= MIN(Dates[Date])

))

Because the Date table is filtered by the month that we have on columns we get a single date for each cell that we can use in our filter (as each month only has one date in our example). Just to be sure the formula also works when more then one date is selected I use MAX and MIN instead of values, that way we can easily switch to different granularities if we want to later.

There is one flaw of this approach. The Dates[Date] column right now is the beginning of the month, that will never give results for the entire month. So I added a calculated column to the Date table that give me the end date of the current month:

=EOMONTH([Date],0)

Now to use it in the function

Active Facts:=CALCULATE([Nr facts],
FILTER(Facts,
Facts[Acquired] <= MAX(Dates[EoMonthDate])
&& Facts[Disposed] >= MIN(Dates[Date])

))

This will give me the number of ”Facts” grouped by Month and Class:

I can now swap month for year and still get the right results (because of using MAX / MIN instead of values, thanks Rob Collie for setting this trend )

Or even add Asset on Rows instead of class and get the right results:

Hope this gave you a little more insight into DAX. You can download the Excel 2013 workbook here: https://skydrive.live.com/redir?resid=7F4E0559CC74581A!1088