我們在做BI報(bào)表分析的時候，大部分指標(biāo)的運(yùn)算邏輯都不是特別的復(fù)雜，很多用戶對于指標(biāo)最基本的需求，就是可以展示當(dāng)月數(shù)據(jù)，看個同環(huán)比，能看個YTD就夠了。
需求聽上去并不是特別的復(fù)雜，但是這里面也是有一些小技巧的。
先來看看本期的案例數(shù)據(jù)：
案例數(shù)據(jù)就一張Sales的銷售事實(shí)表，表結(jié)構(gòu)也相對簡單，將其導(dǎo)入到PowerBI中。
添加如下日期表，并建立模型關(guān)系。
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
)
)
添加如下基礎(chǔ)度量值：
Quantity =
SUM ( Sales[Quantity] )
將其放入到表格中展示如下：
到這里，我們的準(zhǔn)備工作完成。
那么有關(guān)同環(huán)比的計(jì)算有哪些方式呢？
思考一下
1
2
3
......
其實(shí)，主要方法就兩種，一種是通過時間智能函數(shù)計(jì)算，另一種是通過Calculate+Filter+All的方式進(jìn)行條件計(jì)算。
（注：快速度量值的方法這里就不贅述了。）
時間智能函數(shù)計(jì)算：
環(huán)比計(jì)算：
時間智能環(huán)比% =
VAR LastMonth =
CALCULATE ( [Quantity], DATEADD ( 'Date'[Date], -1, MONTH ) )
VAR Result =
DIVIDE ( [Quantity] - LastMonth, LastMonth )
RETURN
Result
同比計(jì)算：
方法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
結(jié)果如下：
從結(jié)果上看，時間智能函數(shù)的內(nèi)部邏輯構(gòu)造相對而言，較為簡單，適用于初學(xué)者。
不過，這里白茶要特殊說明一下，如果在實(shí)際項(xiàng)目的開發(fā)中，白茶是不建議大家去使用時間智能類型的函數(shù)做指標(biāo)計(jì)算的。
某些情況下，日期數(shù)據(jù)由于事實(shí)表中斷，這時候使用時間智能函數(shù)做計(jì)算，結(jié)果上會有差異，而且不利于新手去排查這個問題所在。
特別是TotalYTD，TotalQTD，TotalMTD這三個函數(shù)，小伙伴們要慎用。
條件計(jì)算：
環(huán)比計(jì)算：
條件計(jì)算環(huán)比% =
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
同比計(jì)算：
條件計(jì)算同比% =
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
結(jié)果如下：
從結(jié)果上看，二者是一樣的，但是從內(nèi)部邏輯構(gòu)造的角度，相對于時間智能函數(shù)要更為復(fù)雜。但是白茶想說的是，懂的都懂，遇到了就明白了這種寫法的好處了。
TotalMTD、TotalQTD、TotalYTD也可以參照此邏輯去寫。
當(dāng)然，這種寫法也是可以進(jìn)行優(yōu)化的，我們先對之前的日期表進(jìn)行一下處理。
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 ----新增列
)
)
白茶在此基礎(chǔ)上，新增了一列，結(jié)果如下：
接下來，我們可以通過此列，進(jìn)行同環(huán)比計(jì)算。
環(huán)比計(jì)算：
Count環(huán)比% =
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
同比計(jì)算：
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
結(jié)果如下：
Demo文件在知識星球。
這里是白茶，一個PowerBI的初學(xué)者。
