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.