使用 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.

问题是，Monthly Revenue 是一个度量.我还想为 Annual Revenue 创建一个度量，以便它可以与其他过滤器交互.但是，我只知道如何使用 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 月至 2015 年 4 月).我通常不会使用 [Monthly Revenue] 度量和 Month 表来执行此操作.我会使用常规日期表、基本收入度量和 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 表，这就是我要做的度量:

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屋！