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.

Feb 28, 2019 in Power BI 6,901 views

## 1 answer to this question.

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]`
• 13,490 points

## Dax: Sum of values from many columns in a row

Yes, you can. Try to "Unpivot other columns" ...READ MORE

## Can i show data for one variable for a field without using slicer?

Hi Pranav, Yes, you can perform the calculation ...READ MORE

## How to rename a column using DAX in Power BI?

Hi Ramya, I found these two function that ...READ MORE

## Combine tables in Power BI

You can also achieve this using a ...READ MORE

## Power Bi Dax Table

You need to wrap the numbers in ...READ MORE

## Power BI Dax Multiple IF AND Statements

HI I have Column Patient 12 13 14 15 18 IN Patient=12, 13 Out ...READ MORE

## Distinct count filtered by condition using Power BI Dax

Try this, it should work: DistinctCountActiveMonths = CALCULATE( ...READ MORE

## Embedding a dashboard using Java in Power BI

Make sure to use the correct embedURL, ...READ MORE

## Using Treemap as a filter

What you want is possible using the ...READ MORE