基于类别的同一列中的多个日期之间的差异 [英] 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

货到付款 |日期 |days_diff

B |05/01/2018 |

B |05/01/2018 |

B |09/01/2018 |4

B |09/01/2018 | 4

B |2018 年 12 月 1 日 |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屋！