powerbi - 使用 DAX 计算每个 ID 的最新日期

Question

我在 Power BI 中有这样的数据集：

ID      FirstDate    LastDate
214443  07/06/2016  07/06/2017
214443  09/11/2016  09/11/2017
214443  28/09/2018  11/06/2019
214443  31/05/2019  11/06/2019

我想创建两个计算列，其中包含每个索引的最新日期。目前，我只能获取所有数据集的最新日期或每一行的相同日期。

输出应该是这样的：

ID      FirstDate   LastDate    FirstDate2  LastDate2
214443  07/06/2016  07/06/2017      
214443  09/11/2016  09/11/2017  07/06/2016  07/06/2017
214443  28/09/2018  11/06/2019  09/11/2016  09/11/2017
214443  31/05/2019  11/06/2019  28/09/2018  11/06/2019

谢谢！！

标签： powerbidaxpowerbi-desktop

Answer 1

我从这样的数据开始，

然后使用查询编辑器添加索引列，

然后创建一个列来检查单个 ID 存在的次数：-

Number of ID's Present = COUNTROWS(FILTER('MyTable', 
     (EARLIER('MyTable'[ID]) = 'MyTable'[ID])))

然后想知道每个 ID 的起始索引以使其变得容易：-

Starting Index = CALCULATE(MIN(MyTable[Index]),FILTER('MyTable', 
     (EARLIER('MyTable'[ID]) = 'MyTable'[ID])))

然后现在你可以用一些简单的方法来做你正在寻找的事情，

First Date 2 = 
    Var NumberofIDsPresent = MyTable[Number of ID's Present]
    Var StartingIndex = MyTable[Starting Index]
    Var CurrentIndex = MyTable[Index]

    Var Only_OneTime_ID_Present = IF(OR(NumberofIDsPresent = 1,CurrentIndex = StartingIndex),1,0)
    Var Multiple_Times_ID_Present = CALCULATE(MIN(MyTable[First Date]),FILTER(MyTable, MyTable[Index] = CurrentIndex - 1))
    var result = IF(Only_OneTime_ID_Present = 1, BLANK(),Multiple_Times_ID_Present)
    return result


Last Date 2 = 
    Var NumberofIDsPresent = MyTable[Number of ID's Present]
    Var StartingIndex = MyTable[Starting Index]
    Var CurrentIndex = MyTable[Index]

    Var Only_OneTime_ID_Present = IF(OR(NumberofIDsPresent = 1,CurrentIndex = StartingIndex),1,0)
    Var Multiple_Times_ID_Present = CALCULATE(MIN(MyTable[Last Date]),FILTER(MyTable, MyTable[Index] = CurrentIndex - 1))
    var result = IF(Only_OneTime_ID_Present = 1, BLANK(),Multiple_Times_ID_Present)
    return result

现在，这使我的数据看起来像这样，

如果有帮助，请接受答案，如果不能解决您的问题，请告诉我。