使用Power BI中的Measure从每月总计中计算年度总计 [英] Calculate annual total from monthly total using Measure in Power BI

问题描述

我想根据月度收入数据来计算每个月的年收入。示例数据如下所示：

I want to calculate annual revenue for each month from monthly revenue data. Sample data is shown as below:

例如对于2015年5月的年收入= 2015年1月至2015年5月的月收入总和，依此类推。

e.g. For annual revenue of 2015 May = sum of monthly revenue from 2015 Jan to 2015 May, and so on.

问题是，月收入是一种度量。我也想为年收入创建一个度量，以便它可以与其他过滤器交互。但是，我只知道如何使用 Calculated Column 来写表达式：

The problem is, the Monthly Revenue is a measure. I want to create a measure for Annual Revenue as well, so that it can interact with other filters. However, I only know how to write the expression using Calculated Column:

Annual Revenue = 
CALCULATE(
    [Monthly Revenue],
    FILTER(
        'Month',
        'Month'[Year] = EARLIER('Month'[Year]) &&
        'Month'[MonthKey] <= EARLIER('Month'[MonthKey])
    )
)

我如何转换上面的表达式，以便它与 Measure 一起使用？

How can I translate the above expression so that it will work with Measure?

推荐答案

听起来，您想要的是给定日期的YTD度量（即2015年5月，YTD是2015年1月至4月）。通常，我不会使用[每月收入]指标和月份表来执行此操作。我会使用常规日期表，基本收入度量和DATESYTD来完成此操作。

It sounds like what you want is a YTD measure for any given date (i.e. in May 2015, YTD is January-April 2015). I typically wouldn't do this using a [Monthly Revenue] measure and a Month table. I'd do this using a regular date table, a base Revenue measure, and DATESYTD.

但是，使用您概述的MONTH表，这就是我'd做一个度量：

However, using the MONTH table as you've outlined, this is what I'd do for a measure:

Annual Revenue Measure =
CALCULATE (
    [Monthly Revenue],
    FILTER (
        ALL ( 'Month' ),
        'Month'[Year] = MAX ( 'Month'[Year] )
            && 'Month'[MonthKey] <= MAX ( 'Month'[MonthKey] )
    )
)

您会注意到，它与计算列几乎相同，只是使用MAX而不是EARLIER（因为EARLIER仅适用于计算列）。

You'll note it's almost the same as you have for your calculated column, except using MAX rather than EARLIER (since EARLIER only applies to calculated columns).

这篇关于使用Power BI中的Measure从每月总计中计算年度总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！