BI技巧丨同环比计算

最新推荐文章于 2024-09-27 16:32:32 发布

Fabric丨白茶

最新推荐文章于 2024-09-27 16:32:32 发布

阅读量1.1k

点赞数 1

分类专栏： PowerBI丨应用篇文章标签：大数据

本文链接：https://blog.csdn.net/sterln/article/details/125821121

版权

PowerBI丨应用篇专栏收录该内容

128 篇文章 49 订阅

订阅专栏

我们在做BI报表分析的时候，大部分指标的运算逻辑都不是特别的复杂，很多用户对于指标最基本的需求，就是可以展示当月数据，看个同环比，能看个YTD就够了。

需求听上去并不是特别的复杂，但是这里面也是有一些小技巧的。

先来看看本期的案例数据：

案例数据就一张Sales的销售事实表，表结构也相对简单，将其导入到PowerBI中。

添加如下日期表，并建立模型关系。

Date =
GENERATE (
    CALENDAR ( MIN ( 'Sales'[DATE] ), MAX ( 'Sales'[DATE] ) ),
    VAR DA = [Date]
    VAR YEAR =
        YEAR ( DA )
    VAR QUARTER =
        "Q" & FORMAT ( DA, "Q" )
    VAR MONTE =
        FORMAT ( DA, "MM" )
    VAR DAY =
        DAY ( DA )
    RETURN
        ROW (
            "Year", YEAR,
            "Quarter", QUARTER,
            "Month", MONTE,
            "DayOfMonth", DAY,
            "YearQuarter", YEAR & QUARTER,
            "YearMonth", YEAR & MONTE
        )
)

添加如下基础度量值：

Quantity =
SUM ( Sales[Quantity] )

将其放入到表格中展示如下：

到这里，我们的准备工作完成。

那么有关同环比的计算有哪些方式呢？

思考一下
1
2
3
…

其实，主要方法就两种，一种是通过时间智能函数计算，另一种是通过Calculate+Filter+All的方式进行条件计算。

（注：快速度量值的方法这里就不赘述了。）

时间智能函数计算：

环比计算：

时间智能环比% =
VAR LastMonth =
    CALCULATE ( [Quantity], DATEADD ( 'Date'[Date], -1, MONTH ) )
VAR Result =
    DIVIDE ( [Quantity] - LastMonth, LastMonth )
RETURN
    Result

同比计算：

方法1：

时间智能同比one% =
VAR LastMonth =
    CALCULATE ( [Quantity], DATEADD ( 'Date'[Date], -1, YEAR ) )
VAR Result =
    DIVIDE ( [Quantity] - LastMonth, LastMonth )
RETURN
    Result

方法2：

时间智能同比two% =
VAR LastMonth =
    CALCULATE ( [Quantity], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
VAR Result =
    DIVIDE ( [Quantity] - LastMonth, LastMonth )
RETURN
    Result

结果如下：

从结果上看，时间智能函数的内部逻辑构造相对而言，较为简单，适用于初学者。

不过，这里白茶要特殊说明一下，如果在实际项目的开发中，白茶是不建议大家去使用时间智能类型的函数做指标计算的。

某些情况下，日期数据由于事实表中断，这时候使用时间智能函数做计算，结果上会有差异，而且不利于新手去排查这个问题所在。

特别是TotalYTD，TotalQTD，TotalMTD这三个函数，小伙伴们要慎用。

条件计算：

环比计算：

条件计算环比% =
VAR CurrentYear =
    SELECTEDVALUE ( 'Date'[Year] )
VAR CurrentMonth =
    SELECTEDVALUE ( 'Date'[Month] )
VAR YearNum =
    IF ( CurrentMonth = "01", CurrentYear - 1, CurrentYear )
VAR LastMonth =
    IF ( CurrentMonth = "01", "12", CurrentMonth - 1 )
VAR LastMonthNum =
    FORMAT ( YearNum * 100 + LastMonth, "" )
VAR LastMonthValue =
    CALCULATE (
        [Quantity],
        FILTER ( ALL ( 'Date' ), 'Date'[YearMonth] = LastMonthNum )
    )
VAR Result =
    DIVIDE ( [Quantity] - LastMonthValue, LastMonthValue )
RETURN
    Result

同比计算：

条件计算同比% = 
VAR CurrentYear =
    SELECTEDVALUE ( 'Date'[Year] )
VAR CurrentMonth =
    SELECTEDVALUE ( 'Date'[Month] )
VAR LastYear = CurrentYear - 1
VAR LastYearMonth =
    FORMAT ( LastYear * 100 + CurrentMonth, "" )
VAR LastYearMonthValue =
    CALCULATE (
        [Quantity],
        FILTER ( ALL ( 'Date' ), 'Date'[YearMonth] = LastYearMonth )
    )
VAR Result =
    DIVIDE ( [Quantity] - LastYearMonthValue, LastYearMonthValue )
RETURN
    Result

结果如下：

从结果上看，二者是一样的，但是从内部逻辑构造的角度，相对于时间智能函数要更为复杂。但是白茶想说的是，懂的都懂，遇到了就明白了这种写法的好处了。

TotalMTD、TotalQTD、TotalYTD也可以参照此逻辑去写。

当然，这种写法也是可以进行优化的，我们先对之前的日期表进行一下处理。

Date =
GENERATE (
    CALENDAR ( MIN ( 'Sales'[DATE] ), MAX ( 'Sales'[DATE] ) ),
    VAR DA = [Date]
    VAR YEAR =
        YEAR ( DA )
    VAR QUARTER =
        "Q" & FORMAT ( DA, "Q" )
    VAR MONTE =
        FORMAT ( DA, "MM" )
    VAR DAY =
        DAY ( DA )
    RETURN
        ROW (
            "Year", YEAR,
            "Quarter", QUARTER,
            "Month", MONTE,
            "DayOfMonth", DAY,
            "YearQuarter", YEAR & QUARTER,
            "YearMonth", YEAR & MONTE,
            "YearMonthCount",
                YEAR * 12 + MONTE   ----新增列
        )
)

白茶在此基础上，新增了一列，结果如下：

接下来，我们可以通过此列，进行同环比计算。

环比计算：

Count环比% = 
VAR CurrentCount =
    SELECTEDVALUE ( 'Date'[YearMonthCount] )
VAR LastMonthCount = CurrentCount - 1
VAR LastMonthValue =
    CALCULATE (
        [Quantity],
        FILTER ( ALL ( 'Date' ), 'Date'[YearMonthCount] = LastMonthCount )
    )
VAR Result =
    DIVIDE ( [Quantity] - LastMonthValue, LastMonthValue )
RETURN
    Result

同比计算：

Count同比% =
VAR CurrentCount =
    SELECTEDVALUE ( 'Date'[YearMonthCount] )
VAR LastYearMonthCount = CurrentCount - 12
VAR LastYearMonthValue =
    CALCULATE (
        [Quantity],
        FILTER ( ALL ( 'Date' ), 'Date'[YearMonthCount] = LastYearMonthCount )
    )
VAR Result =
    DIVIDE ( [Quantity] - LastYearMonthValue, LastYearMonthValue )
RETURN
    Result

结果如下：