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:

First Activity Date:=LOOKUPVALUE(

                 DimDate[FullDateAlternateKey],

                 DimDate[DateKey],

                 MIN(FactInternetSales[OrderDateKey])

)

image

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:

image

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:

image

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

)

image

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:

image

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

)

 

image

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.   

Advertisements

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)
      at Microsoft.AnalysisServices.Modeler.FieldList.InProcServer.LoadSafeSandboxAfterConnection()
      at Microsoft.AnalysisServices.Modeler.FieldList.InProcServer.LoadOLEDBConnection(Boolean raiseCompleteEvent)

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

      • javierguillen says:

        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.

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: