Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

YoY % Change Matrix Total Calc

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%
  • Hi Anonymous ,

     

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

    Here is my test file for your reference.

     

2 Replies

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support

    Hi Anonymous ,

     

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

    Here is my test file for your reference.

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      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))?