2nd latest Date - DAX

Question

I have a dataset of users who log into an app. I want to find the # of days between their last two logins. I have the DAX expression to get their last login (latest date)

=CALCULATE(Max([Date]),ALL(Table1),Table1[Name]=EARLIER(Table1[Name]))

But now I'd like to get their 2nd to last login, and subtract the two. I see some posts about the 2nd to last login, but it puts a blank if there are only two logins, whereas I want the number of days between these as well.

Answer 1

dcheney,

this one is tricky, but doable. It might be a bit difficult to understand but given you have already used EARLIER function, you are very close to your desired result with calculating the day difference between last and second-to-last date of login.

So assuming your source data look like this:

ID  User    Day
    1   1   1-Jan
    2   1   10-Jan
    3   2   2-Feb
    4   2   3-Feb
    5   2   7-Feb

I would start with creating a new calculated column that would sort of rank each visit for specific user. This formula should do it:

=CALCULATE (
    COUNTROWS ( 'datatable' ),
    'datatable'[User] = EARLIER ( 'datatable'[User] ),
    'datatable'[Day] < EARLIER ( 'datatable'[Day] ),
    ALL ( 'datatable' )
)
    + 1

This will rank add the user based rank to you datatable:

ID  User    Day CountLoginNumber
1   1   1/1/2014 12:00:00 AM    1
2   1   1/10/2014 12:00:00 AM   2
3   2   2/2/2014 12:00:00 AM    1
4   2   2/3/2014 12:00:00 AM    2
5   2   2/7/2014 12:00:00 AM    3

With this done, there is one more magic formula for another nested column (I have named it Date of Last Login) that does all the heavy lifting:

=
IF (
    AND (
        [CountLoginNumber] > 1,
        [CountLoginNumber]
            = CALCULATE (
                COUNTROWS ( 'datatable' ),
                'datatable'[User] = EARLIER ( 'datatable'[User] ),
                ALL ( 'datatable' )
            )
    ),
    CALCULATE (
        LASTDATE ( 'datatable'[Day] ),
        'datatable'[User] = EARLIER ( 'datatable'[User] ),
        ALL ( 'datatable' )
    )
        - CALCULATE (
            LASTDATE ( 'datatable'[Day] ),
            'datatable'[User] = EARLIER ( 'datatable'[User] ),
            'datatable'[CountLoginNumber]
                < EARLIER ( 'datatable'[CountLoginNumber] ),
            ALL ( 'datatable' )
        ),
    BLANK ()
)

Honestly, this is one of the longest formula I have ever written in Powerpivot. You could do it with separated calculated columns, but I am not a big fan of that. This is what the formula basically does:

1. IF clause checks whether there is more than 1 login AND if the date of login also equals to the last known login for each user (I want to calculate the date difference only for the last known date).
2. IF the above mentioned conditions are TRUE, then there are 2 CALCULATE formulas - the first one calculate the last date of login for each user; the second one calculates the previous one for the very same user). If you subtract those two dates, you get the desired result.
3. Then there is also BLANK() function which is executed when the IF conditions are not TRUE. Just in case :-)

The resulting table then looks like this:

ID  User    Day CountLoginNumber    Date of Last Login
1   1   1/1/2014 12:00:00 AM    1   
2   1   1/10/2014 12:00:00 AM   2   9
3   2   2/2/2014 12:00:00 AM    1   
4   2   2/3/2014 12:00:00 AM    2   
5   2   2/7/2014 12:00:00 AM    3   4

With that, you can then create a simple (Power)pivot table to do all the following (analytic) work that needs to be done.

Check out my source file in Excel (2013) if needed. Hope this helps!