同一列中基于类别的多个日期之间的差异 [英] Difference between multiple dates in the same column based on category

问题描述

我有以下问题..

我有此表：

我想创建一个计算列，告诉我相同代码的两个日期之间的天差（ COD ），则应根据行前的日期计算差异。

I want to create a calculated column that tells me the difference of days between two dates of the same code (COD), the difference should be calculated based on the date before the line.

例如：

使用 COD B

化学需氧量| DATE | days_diff

B | 05/01/2018 |

B |05/01/2018 |

B | 09/01/2018 | 4

B |09/01/2018 | 4

B | 12/01/2018 | 3

B |12/01/2018 | 3

在示例图像中，代码/日期是按顺序排序的，但实际上它们是乱序的。

In the example image the codes / dates are sorted in sequence, but in reality they are out of order.

我尝试在DAX中使用以下句子：

I tried to use the following sentence in DAX:

 DATEDIFF(Testing[DATE]; FIRSTDATE(FILTER( ALL(Testing[DATE]) ;Testing[DATE] > EARLIER(Testing[DATE])));DAY)

解释我的尝试：

使行上的日期与使用日期之间的差 EARLIER 函数从当前日期中获取最新日期。

Make the difference between the date on the line and using the EARLIER function to get the most recent date out of the current one.

但是，我获得了以下结果：

However, I obtained the following result:

我无法过滤 COD ，因此， EARLIER的分析仅在同一组中进行，因此我了解PowerBI正在考虑所有日期。

I can not filter COD, so that the analysis of 'EARLIER' is performed only in the same 'group', so I understand the PowerBI is considering all the dates.

有什么想法吗？

推荐答案

您的想法很接近，但是需要一些调整。试试这个：

You're pretty close in idea, but it needs some tweaking. Try this:

Days_diff =
VAR StartDate =
    CALCULATE (
        LASTDATE ( Testing[DATE] ),
        FILTER (
            ALLEXCEPT ( Testing, Testing[COD] ),
            Testing[DATE] < EARLIER ( Testing[DATE] )
        )
    )
RETURN
    DATEDIFF ( StartDate, Testing[DATE], DAY )

变量 StartDate 计算当前行日期之前的最后一个日期。我使用 CALCULATE 删除除 COD 之外的所有行上下文，因为这是我们要进行分组的内容。

The variable StartDate calculates the last date before the current row date. I use CALCULATE to remove all row context except for COD since that is what we are grouping on.

注意： EARLIER 函数不是日期/时间函数，而是对较早行上下文的引用（在进入内部之前 FILTER 函数）。

Note: The EARLIER function is not a date/time function, but rather a reference to an earlier row context (before stepping inside the FILTER function). It allows us to jump back a level when we are nesting functions.

然后，您只需使用 DATEDIFF 。

这篇关于同一列中基于类别的多个日期之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！