时间戳

首页 > 解决方案 > 如何根据列中的值名称获取较早的值

dax - 如何根据列中的值名称获取较早的值

问题描述

我想根据属性名称获取前一天的值，因此将两级过滤器应用于我的计算列。我目前坚持使用以下公式给我一个空白列。

Column = CALCULATE(
                    MAX(useractivity[Value]),
                    FILTER(
                            useractivity,
                            useractivity[Dates]=EARLIER(useractivity[Dates])-1
                        ),
                    FILTER(
                        an IFS function maybe?
                    ))

我想要以下输出：

+------------------+-----------+---------+----------------+
|      Dates       | Attribute |  Value  | Previous_Value |
+------------------+-----------+---------+----------------+
| 31 december 2018 | active    | 100,000 |         80,000 |
| 31 december 2018 | inactive  |  50,000 |         40,000 |
| 29 december 2018 | active    |  80,000 |            ... |
| 29 december 2018 | inactive  |  40,000 |            ... |
+------------------+-----------+---------+----------------+

标签： dax

解决方案

这与我在这里的回答相似。计算上一个日期，然后进行查找。

Previous_Value =
VAR PrevDay =
    CALCULATE (
        LASTDATE ( useractivity[Dates] ),
        ALLEXCEPT ( useractivity, useractivity[Attribute] ),
        useractivity[Dates] < EARLIER ( useractivity[Dates] )
    )
RETURN
    LOOKUPVALUE (
        useractivity[Value],
        useractivity[Dates], PrevDay,
        useractivity[Attribute], useractivity[Attribute]
    )

编辑

作为衡量标准，您需要进行一些小的调整，

Previous_Value = 
VAR PrevDay =
    CALCULATE (
        LASTDATE ( useractivity[Dates] ),
        FILTER(
            ALLEXCEPT ( useractivity, useractivity[Attribute] ),
            useractivity[Dates] < MAX( useractivity[Dates] )
        )
    )
RETURN
    LOOKUPVALUE (
        useractivity[Value],
        useractivity[Dates], PrevDay,
        useractivity[Attribute], MAX(useractivity[Attribute])
    )

推荐阅读