问题描述
我想根据月度收入数据来计算每个月的年收入。示例数据如下所示：
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从每月总计中计算年度总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！