Calculating MoM Variance of a Measure Field using DAX

0 votes

 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 in Power BI by Upasana
• 8,110 points
184 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
0 votes
  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]
answered Feb 28 by Shubham
• 12,710 points

Related Questions In Power BI

0 votes
1 answer

Dax: Sum of values from many columns in a row

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

answered Apr 3 in Power BI by Upasana
• 8,110 points
29 views
0 votes
1 answer

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

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

answered May 16 in Power BI by CHERUKURI
• 14,860 points
12 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,190 points
77 views
0 votes
1 answer

list reports with calculated percentage in Power BI using dax

Create a Measure called Total Revenue: Total Revenue = SUMX( ...READ MORE

answered Oct 22, 2018 in Power BI by Hannah
• 14,080 points
61 views
+3 votes
2 answers

Combine tables in Power BI

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

answered Oct 5, 2018 in Power BI by lina
• 8,100 points

edited Oct 11, 2018 by Kalgi 95 views
0 votes
1 answer

Power Bi Dax Table

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

answered Oct 5, 2018 in Power BI by Kalgi
• 36,220 points
47 views
0 votes
2 answers

Power BI Dax Multiple IF AND Statements

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

answered May 22 in Power BI by VNK
4,401 views
0 votes
1 answer

Distinct count filtered by condition using Power BI Dax

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

answered Oct 5, 2018 in Power BI by Kalgi
• 36,220 points
2,666 views
0 votes
1 answer

Embedding a dashboard using Java in Power BI

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

answered Nov 30, 2018 in Power BI by Shubham
• 12,710 points
534 views
0 votes
1 answer

Using Treemap as a filter

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

answered Dec 17, 2018 in Power BI by Shubham
• 12,710 points
160 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.