YoY % Change Matrix Total Calc

[Anonymous]

Hello I created a YoY measure that outputs blank values if the YoY divison formula outputs a -1 or 0. This works fine for graphs, but when I use this measure in a matrix, the total is very off. This is because I do not have complete data for both years, -- there are some months without data. 

So, how do I get the total displayed in the matrix to only do the YoY calc for months for which I have both 2018 and 2019 data, and ignore months that don't overlap.

Here is the measure I am using:

SelectedMetric YoY% =

VAR __PREV_YEAR = CALCULATE([SelectedMetric], DATEADD('dimdate2'[Date].[Date], -1, YEAR))

    RETURN

        if(or(DIVIDE([SelectedMetric] - __PREV_YEAR, __PREV_YEAR) = -1,DIVIDE([SelectedMetric] - __PREV_YEAR, __PREV_YEAR) = 0),BLANK(),DIVIDE([SelectedMetric] - __PREV_YEAR, __PREV_YEAR))

and here is a screenshot of the output in a matrix, with location names removed. I have also removed the second totals column in the matrix menu.

for example: I would like the last line total to show "171.44%" instead of 54.55%

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could use IF() and ISINSCOPE() funcitions to change the last value.

Here is my test file for your reference.

[userdata]

Hi,

could anybody just write the applied measure or explain the measure. I do not understand why we need a primary key here index to refer to in column1, do we not have to reference to date and the the selected measure as well as year to date measure applied to above problem? So like measure = a= max (date(date)

                                       return IF(ISINSCOPE(date(date, calculate (selectedmeasure), filter (date(date), selectedmetrics))?