Need help in showing MoM variance

[Anonymous]

Hi, Actually I have a scenario where I need to show MoM variance% of the views of different reports. I have created two measures for that.

one for TotalViews

TotalViews = COUNTROWS(DISTINCT(consumer[session]))

 Second for variance

Variance% = DIVIDE( [TotalViews], CALCULATE( [TotalViews], DATEADD (DimDate[Date],-1,MONTH)), 0)-1

 when, I put these measures in a Matrix visual, the result is:

	Jan		Feb		Mar		total
Report Name	Views	var%	Views	var%	Views	var%	Views	var%
ABC	11	-100.00%	15	36.36	13	-13.33%	39	0.00%
DEF		-100.00%		-100.00%	7	-100.00%	7	0.00%
GHI	14	-100.00%	36	157.14%	60	66.67%	110	0.00%
JKL	10	-100.00%		-100.00%	216	-100.00%	226	0.00%
Total	35	-100.00%	51	45.71%	296	480.39%	382	0.00%

As you can see when I have no views/ blank values, the variance showing -100.00% -

• How to get 0.00% in var% if i have to blank values.

• Also the var% is showing incorrect percentage, 

Can someone please help me with this?

Thank you.

[Anonymous · Accepted solution]

Hi @Anonymous,

You can try to use the following measure formula if help:

Variance% =
VAR currDate =
    MAX ( DimDate[Date] )
VAR prevDate =
    DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, DAY ( currDate ) )
VAR currCount =
    COUNTROWS ( VALUES ( consumer[session] ) )
RETURN
    IF (
        currCount <> 0,
        DIVIDE (
            currCount,
            CALCULATE (
                COUNTROWS ( VALUES ( consumer[session] ) ),
                FILTER (
                    ALLSELECTED ( consumer ),
                    YEAR ( [Date] ) = YEAR ( prevDate )
                        && MONTH ( [Date] ) = MONTH ( prevDate )
                )
            )
        )
    )

If these also don't help, please share some dummy data with raw table structure to help us clarify your scenario and test to coding format.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng