Power BI（DAX）：计算测量字段的MoM方差 [英] Power BI (DAX): Calculating MoM Variance of a Measure Field

问题描述

我有一个度量公式，该度量公式可以将表格转换为不同客户的每月计数：

I have measure formula that takes a table and converts it to monthly count of distinct customers:

Active Publishers =
CALCULATE(
DISTINCTCOUNT( 'Net Revenue Data'[Publisher Name] ),
'Net Revenue Data'[Active Month] = 1)

现在，我想创建一个采用此趋势的月度（MoM）方差的新公式，例如：

Now, I would like to create a new formula that takes the Month-Over-Month (MoM) variance of this trend, like this:

这是我尝试获取每月净变化的公式：

This is the formula I attempted to get the net monthly change:

Net Change = 'Net Revenue Data'[Active Publishers] - 
CALCULATE('Net Revenue Data'[Active Publishers], 
PREVIOUSMONTH('Net Revenue Data'[Date (Month)]))

如何创建度量

我创建了另一个与收入表的日期列相关的日期表：

I created an additional date table to relate to the revenue table's date column:

Date Table = 
ADDCOLUMNS(CALENDAR("1/1/2000","12/31/2025"),"DateAsInteger",FORMAT([Date],"YYYYMDD"),  "Year",YEAR([Date]),  "MonthNumber",FORMAT([Date],"MM"),"YearMonthNumber",FORMAT([Date],"YYYY/MM"),"Ye arMonthShort",FORMAT([Date],"YYYY/mmm"),"MonthNameShort",FORMAT([Date],"mmm"),"MonthNameLong",FORMAT([Date],"mmmm"),"DayOfWeekNumber",WEEKDAY([Date]),"DayOfWeek",FORMAT([Date],"dddd"),"DayOfWeekShort",FORMAT([Date],"dddd"),"Quarter","Q"&FORMAT([Date],"Q"),"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))

现在我应该可以将两个表关联起来

Now I should be able to relate the two tables to create Month over Month variance.

推荐答案

一个解决此问题的方法需要2个步骤：

One solution to this problem requires 2 steps:

1. 创建一个中间计算列，该列采用上个月的活动发布者：

1. Create an intermediate calculated column that takes the last month's active publishers:

LM Active Pubs = CALCULATE（ [Active Pubs]，DATEADD（'Net Rev 09-14'[Date]，-1，MONTH））

LM Active Pubs = CALCULATE([Active Pubs],DATEADD('Net Rev 09-14'[Date],-1,MONTH))

减去当前月份的 Active Pubs来自上个月活跃发布商的信息：

Subtract the current months "Active Pubs" from the last month's active publishers:

活跃酒吧的变化= [活跃酒吧]-[LM活跃酒吧]

Change in Active Pubs = [Active Pubs]-[LM Active Pubs]

这篇关于Power BI（DAX）：计算测量字段的MoM方差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！