过去21天的滚动阿夫格

Question

我如何创建一个度量来计算过去21天的平均支出？下面的公式返回null

Average of Spend rolling average = 
    VAR __LAST_DATE = LASTDATE('Raw_Data'[Date_1].[Day])
    RETURN
        AVERAGEX(
            DATESBETWEEN(
                'Raw_Data'[Date_1].[Day],
                DATEADD(__LAST_DATE, -21, DAY),
                DATEADD(__LAST_DATE, 0, DAY)
            ),
            CALCULATE(AVERAGE('Raw_Data'[Spend]))
        )

我不能创造价值。它返回0。

Answer 1

除了使用Date_1.Day和DATESBETWEEN出现的错误之外，这看起来还不错。例如这个

Average of Spend rolling average = 
    VAR __LAST_DATE = LASTDATE('Raw_Data'[Date_1])
    RETURN
        AVERAGEX(
            DATESBETWEEN(
                'Raw_Data'[Date_1],
                DATEADD(__LAST_DATE, -20, DAY),
                DATEADD(__LAST_DATE, 0, DAY)
            ),
            CALCULATE(AVERAGE('Raw_Data'[Spend]))
        )

似乎对我是正确的

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1MAQiJR0lU6VYHSQhI6CQoQGqmDFQzBhNzASkFU3MFKwXKBgLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date_1 = _t, Spend = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date_1", type date}, {"Spend", Int64.Type}})
in
    #"Changed Type"