第2个最新日期-DAX [英] 2nd latest Date - DAX

问题描述

我有一个登录应用程序的用户数据集。我想找到他们最近两次登录之间的天数。我有DAX表达式来获取他们的最后登录信息（最新日期）

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.

推荐答案

dcheney ，

这很棘手，但是可行。可能有点难以理解，但是鉴于您已经使用了EARLIER函数，因此通过计算登录的最后日期和倒数第二个日期之间的日差，您将非常接近您想要的结果。

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

这将对 user 进行排名根据您的数据表排名：

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

老实说，这是我用Powerpivot编写的最长的公式之一。您可以使用单独的计算列来完成此操作，但是我不赞成这样做。公式基本上是这样做的：

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子句检查是否有多个登录名，以及登录日期是否也等于每个用户的最后一次已知登录名（我只想计算最后一次已知日期的日期差）。


2. 如果上述条件为TRUE，则有2个CALCULATE公式-第一个一个计算每个用户的最后登录日期；第二个为相同的用户计算前一个）。 如果减去这两个日期，则会得到所需的结果。


3. 然后还有 BLANK（）函数，如果IF条件不为TRUE，则执行该函数。以防万一：-）

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

结果表如下所示：

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

然后，您可以创建一个简单的（Power）数据透视表来完成以下所有需要完成的（分析）工作。

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

查看我的如果需要，可使用Excel（2013）中的源文件。希望这会有所帮助！

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

这篇关于第2个最新日期-DAX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！