本文介绍了使用Power BI中的Measure从每月总计中计算年度总计的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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从每月总计中计算年度总计的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-24 22:55