Power BI (DAX): Calculating MoM Variance of a Meas

问题:

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)

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)]))

How can I create a measure that takes the monthly variance of the 'DistinctCountActiveMonths' measure?

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.

回答1:

PREVIOUSMONTH(), like all built-in time intelligence functions in DAX requires a proper date dimension with consecutive, non-repeating dates that span from January 1 of the first year you have data to December 31 of the last year you have data.

I am unsure, looking at the tags on your post whether you are using add-ins to Excel or the Power BI desktop program. If Excel, there is an option in the Power Pivot menu to 'Mark as date table', which you should always do in a Tabular model. If Power BI desktop, this functionality is not yet available, so you must create the relationship between your date dimension and fact table on the date field directly, and not on some surrogate key.

回答2:

One solution to this problem requires 2 steps:

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))

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

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