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.

image

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:

image

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

image

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

image

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

image

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:

image

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:

image

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]:

image

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:

image

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)

image

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:

image

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 Smile)

image

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

image

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

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

  1.  

    Good read, thanks for sharing.
    Was wondering whether 2 filters would have been faster than the formula above (as we are working on 2 smaller tables compared to one big table), something like shown below

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

  2. Hi Jason,

    Yes the query above can give you better performance on large (wide) tables, especially because the filter operation doesn’t have to take all the other columns in the fact table into account but just the two columns you want.

    Thanks,
    Kasper

     
  3.  

    Thanks for the post! After reading Rob’s book, the disconnected table makes for a great pattern to remember.

  4.  

    Excellent Info! Is there an easy way to calculate the average age of the items (either separately or together) for the months that they are active?

  5. Hi @Matt , This should be possible using wrapping the function with an AverageX

     
  6.  

    Thank you for all the excellent information. I have an issue that I have been fighting with for a few days and would like to ask if its possible using the data columns provided in Table 1 of the original post.

    I would like to create a Pivot Chart that would shows, either via a stacked column or line chart with a horizontal axis of months (i.e. January, February etc…), the total number of assets acquired and disposed within a give month.

  7.  

    Its nice approach, but how to fix the totals to show valid values when you want to show the state at particular dates? Like I need to show state on the last day of y2011 and y2012.

  8. Hi Mike, you can use functions like LastDate to get the the values from a specific date.

     

leave your comment


7 × three =