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,530 points
947 views

1 answer to this question.

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
• 13,300 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,530 points
665 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
• 31,840 points
34 views
0 votes
1 answer

How to rename a column using DAX in Power BI?

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

answered Aug 21 in Power BI by anonymous
• 31,840 points
271 views
0 votes
1 answer

Need a DAX : To check the new escalations arrived, on the change of the escalation counter from zero to one or more

Yes exactly !  i need a measure for ...READ MORE

answered Sep 3 in Power BI by anonymous

edited Sep 3 129 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,110 points

edited Oct 11, 2018 by Kalgi 207 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
• 41,990 points
98 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
8,866 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
• 41,990 points
8,518 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
• 13,300 points
935 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
• 13,300 points
445 views