Active day count in DAX

Alberto Ferrari posted a very nice blog entry some time ago regarding how to count active days in PowerPivot (http://sqlblog.com/blogs/alberto_ferrari/archive/2011/05/12/powerpivot-counting-active-days.aspx ).   His example relies on a data model that specifies ‘Active From’ and ‘Active To’ fields in order to compute the count.

I want to write a follow up entry for when such fields do not exist in the data model.  If all you have is a start date reflected as a transaction recorded in your fact table, counting active days is still possible in DAX.  Lets see how.

If we drop the entire list of products in the Adventure Works database in a pivot table, we are able to determine the first ‘active’ day by using the LOOKUPVALUE function:

)

Having tested this, we can now simply use the same technique to count the rows between the first activity date and the last activity date in the fact table.  For this, I use the following expression:

)

This expression is pretty straight forward:  it uses the DATESBETWEEN function for compute the amount of dates between the first date for which there is transaction data for any given product in the fact table and the last date in the fact table across all products.  The result is the following:

The power of this calculation is that it works even if we change the granularity of elements in the row labels.  As an example, if we replace product by category we get:

If what we want is to compute the active days between the first and last dates for which there is transaction activity for any given product or category, we can leverage the same technique we used to get the first activity date, but this time we use it to get the last activity date. We then compute the dates between the two:

)

Notice that is necessary for these calculations to have a proper there is a need to have Date table. That means there should be a list of dates without gaps, otherwise the count would be incorrect.

We can see how these two formulas compare, by placing them side to side.  I have highlighted the rows in which their outputs are different, indicating the products for which the last active date was not the same as the last transaction date in the table:

There is one last scenario that may be of use to know about.  Sometimes we want to calculate the amount of days from the first activity date until the most current date.  In other words, it is a countdown of days until a specific event, for example, the last date for which there was fact data. In this sense, it is the reverse of what we have computed so far.

In order to compute this figure, we can’t rely anymore on the DATESBETWEEN function. Instead, we can simply subtract the two dates, as follows:

)

Clearly, in this last formula we can replace the first date argument with any other arbitrary date for which we need to compute a countdown.  Interestingly, as this expression does not rely on the built-in tabular time intelligence functions, it doesn’t need a date table which was necessary in the prior expressions used in this blog post.

We can use this last technique to dynamically calculate things like countdown to date when payment is due, for example.

10 Responses to Active day count in DAX

1. Sandeep says:

Javier: This article is simply the best. I was trying to re produce the same result in adventure works…I am struck up with the last formula as it is giving me blanks…Can i have the work book..I would like to know where i went wrong…

2. javierguillen says:
• Sandeep says:

Thank you Javier. You have been very helpful.

• Sandeep says:

Javier…I tried to open the excel file…i got some cmd sql dumper exe and data connections disabled and unable to load vertipaq engine..with following error message..I am looking online..to resolve this issue…but any suggestions are helpful..

Error Message:
============================

The handle is invalid

============================
Call Stack:
============================

at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous)
at System.Windows.Forms.Control.Invoke(Delegate method, Object[] args)
at System.Windows.Forms.Control.Invoke(Delegate method)

============================

• javierguillen says:

Hi Sandeep

Send me your email and I will mail you the workbook directly.

• Sandeep says:

Javier…I got the powerpivot to work. The work book has been a great lesson for me. However, If i may be bold enough (that i am not..) the work book is not showing data for some scenarios which exactly mimic what i am currently working on. I can attach the work book, but i do not see the option below. However, the scenario is this…

When i pivot the work book, by selecting EnglishProductName = ‘FR-M94B-46’ or
‘FR-M94B-38’
‘FR-M94B-42’
‘FR-M94B-46’
‘FR-M94S-38’
‘FR-M94S-42’
‘FR-M94S-46’
and so on….(there are exactly 77 of them) on Report Filter, and FullDATEAlternateKey on RowLabels and measure “count down to current date” they do not show value.

I found the reason as below:

SELECT productalternatekey, COUNT(*) TotalCount
FROM dimproduct
GROUP BY productalternatekey
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

For Every Product key we have, we are having few more productalternatekeys. Its like one to many relationship between productkey and productalternatekey. Can you help me solve this scenario. This is exactly i have been looking for. Again I thank you very much for your help.

3. Marco Russo says:

The Countdown to last fact date measure is also useful to get an index for customer retention analysis (ie Customer at risk of leaving fidelity program). Good post!

4. javierguillen says:

That’s a great idea, thanks Marco!

5. javierguillen says:

Sandeep, send me your workbook at javiguillen@hotmail.com and I will take a look.

6. Niraj Krishna says:

I wish I understood DAX like you… You are great! Powerpivot is already doing things for me at my company that they have never been able to see before….