在 Power BI 中使用 DAX 计算运行平均值 [英] Calculate Running Average Using DAX in Power BI

问题描述

我在 Table_1 中有以下值:

date             sales_amount
04/01/2021       100.00
04/02/2021       300.00
04/05/2021       500.00

我想计算一个运行平均值，所以每天都会计算平均值，这样我的最终输出看起来像这样:

I want to compute a running average, so the average is computed as each day passes, so that my final output looks like this:

date             sales_amount       running_average
04/01/2021       100.00             100.00
04/02/2021       300.00             200.00
04/05/2021       500.00             300.00

销售人员没有在 04/03 和 04/04 上工作，所以我想将它们从我的运行平均值中排除.

The sales person did not work on 04/03 and 04/04, so I want to exclude them from my running average.

现在，我的输出看起来像这样，这对我正在做的事情来说是错误的:

Right now, my output looks like this, which is wrong for what I am doing:

date             sales_amount       running_average
04/01/2021       100.00             100.00
04/02/2021       300.00             200.00
04/05/2021       500.00             180.00

有什么建议吗?

现在，我的 DAX 代码如下所示:

Right now, my DAX code looks like this:

test_new = 

VAR LastVisibleDate = MAX('Table_1'[Date])
VAR FirstVisibleDate = MIN('Table_1'[Date])
VAR LastDateWithSales = 
    CALCULATE(
        MAX('Table_1'[Date]),
        REMOVEFILTERS()
    )

VAR Result = 
    IF (
        FirstVisibleDate <= LastDateWithSales,
        CALCULATE(
            AVERAGE([Sales_Amount]),
            Table_1[Date]
        )
    )

RETURN
Result

推荐答案

我在最后一个变量中添加了几行:

I have added a few lines in the last variable:

1. Table_1[Date] 在最后一个变量中基本上意味着 FILTER ( ALL ( Table_1[Date] ), TRUE )，这将返回所有日期，因此在您的场景中没有用，因为它会给出错误的结果，改为写入 Table_1[Date] <= LastVisibleDate

1. Table_1[Date] in the last variable basically means FILTER ( ALL ( Table_1[Date] ), TRUE ), and that will return all of the dates so not useful in your scenario as it will give wrong results, instead write Table_1[Date] <= LastVisibleDate

当您有来自日期表的日期列并且日期表被标记为日期表时，您不需要 REMOVEFILTERS，因为当日期表被标记为日期表时，引擎会自动添加一个 REMOVEFILTERS/ALL 每当在 Date 列上应用过滤器时，但其他表的 Date 列不会发生这种情况，因此您需要在最后一个变量中编写显式 REMOVEFILTERS

You don't need REMOVEFILTERS when you have Date column coming from the Dates table and the Date table is marked as a Date table because when a date table is marked as a date table the engine automatically adds a REMOVEFILTERS/ALL whenever a filter is applied over the Date column, but that won't happen with Date column of other tables hence you need to write an explicit REMOVEFILTERS in the last variable

test_new =
VAR LastVisibleDate =
    MAX ( 'Table_1'[Date] )
VAR FirstVisibleDate =
    MIN ( 'Table_1'[Date] )
VAR LastDateWithSales =
    CALCULATE (
        MAX ( 'Table_1'[Date] ),
        REMOVEFILTERS ()
    )
VAR Result =
    IF (
        FirstVisibleDate <= LastDateWithSales,
        CALCULATE (
            AVERAGE ( [Sales_Amount] ),
            Table_1[Date] <= LastVisibleDate,
            REMOVEFILTERS ( Table_1 )
        )
    )
RETURN
    Result

作为最佳实践，始终使用日期表中的日期列.

As a best practice always use Date column from a Date table.

这篇关于在 Power BI 中使用 DAX 计算运行平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！