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:
-
创建一个中间计算列,该列采用上个月的活动发布者:
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屋!