Calculating MoM Variance of a Measure Field using DAX

Question

 So, I have a measure formula that converts a table it to the 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.

This is the formula I wrote for 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 create relationships between both of these tables to create Month over Month variance.

Answer 1

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

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

2. Subtract the "Active Pubs" of this month from the last month's active publishers:

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