# Active day count in DAX

April 24, 2012 10 Comments

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:

## First Activity Date:=LOOKUPVALUE(

## DimDate[FullDateAlternateKey],

## DimDate[DateKey],

## MIN(FactInternetSales[OrderDateKey])

## )

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:

## Active Days Since Product Inception:=IF(

## CALCULATE( COUNTROWS( FactInternetSales ) ) ,

## CALCULATE(COUNTROWS(DimDate),

## DATESBETWEEN(

## DimDate[FullDateAlternateKey],

## LOOKUPVALUE(

## DimDate[FullDateAlternateKey],

## DimDate[DateKey],

## MIN(FactInternetSales[OrderDateKey])

## ),

## CALCULATE(

## LOOKUPVALUE(

## DimDate[FullDateAlternateKey],

## DimDate[DateKey],

## MAX(FactInternetSales[OrderDateKey])

## ), ALL(FactInternetSales) )

## )

## ) – 1,

## BLANK()

## )

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:

## Active Days:=IF(

## CALCULATE( COUNTROWS( FactInternetSales ) ) ,

## CALCULATE(COUNTROWS(DimDate),

## DATESBETWEEN(

## DimDate[FullDateAlternateKey],

## LOOKUPVALUE(

## DimDate[FullDateAlternateKey],

## DimDate[DateKey],

## MIN(FactInternetSales[OrderDateKey])

## ),

## LOOKUPVALUE(

## DimDate[FullDateAlternateKey],

## DimDate[DateKey],

## MAX(FactInternetSales[OrderDateKey])

## )

## )

## ) – 1,

## BLANK()

## )

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:

## Countdown to last fact date:=IF(

## CALCULATE( COUNTROWS( FactInternetSales ) ) ,

## INT(

## CALCULATE( LOOKUPVALUE(

## DimDate[FullDateAlternateKey],

## DimDate[DateKey],

## MAX(FactInternetSales[OrderDateKey])

## ), ALL(DimDate) ) –

## LOOKUPVALUE(

## DimDate[FullDateAlternateKey],

## DimDate[DateKey],

## MIN(FactInternetSales[OrderDateKey])

## )

## )

## , BLANK()

## )

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.

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…

Hi Sandeep,

I have uploaded my document to my SkyDrive:

https://skydrive.live.com/?cid=3F1BB9738E005DA8&id=3F1BB9738E005DA8%21451&sc=documents

Thank you Javier. You have been very helpful.

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)

at Microsoft.AnalysisServices.Modeler.FieldList.InProcServer.LoadSafeSandboxAfterConnection()

at Microsoft.AnalysisServices.Modeler.FieldList.InProcServer.LoadOLEDBConnection(Boolean raiseCompleteEvent)

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

Hi Sandeep

Do you have the latest version of the PowerPivot add-in installed?

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

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.

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!

That’s a great idea, thanks Marco!

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

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….