Matrix Troubles - Year on Year Column Interaction

[Anonymous]

Hi all,

Hoping to get some help on this if anybody has dealt with something similar to the below before

My pbix file grabs data from SQL and populates a matrix. The data source is a few million rows of data. We are looking to measure year on year performance for certain products and combinations of factors which go into purchasing. I have two core issues which I am trying to solve:

1. My year on year variance (calculation) shows up in both year sections, instead of just one. I would like to remove this column from view
   • Highlighted in red, further down
2. When a product has nil value for this year, the variance displays blank instead of last year's full value. I would like the value to read -$10,000, as the product has dropped from $10k to $0
   • Highlighted in blue, further down

The pbix works as per below:

• Users will select, using a slicer, two sequential years to compare Y1 v Y2
• The matrix uses number values from the data source, and a single calculation for variance

Revenue Variance = [Revenue This Year] - [Revenue Last Year]

• RTY & RLY are as below

Revenue Last Year =
CALCULATE (
    [Total Revenue],
    FILTER (
        ALL ( dt_Example[Fin Year] ),
        dt_Example[Fin Year]
            = MAX ( dt_Example[Fin Year] ) - 1
    )
)


-- and "This Year" is identical except without the - 1

I have put together an ultra simplified dummy data set to demonstrate what is happening. The output looks like the below:

I am able to solve the red issue by changing the layout of matrix (removing year column indicator, replacing revenue data with calculated fields), however this creates a new problem:

• For product "Plum", 2019 is recognized by the calculation as max value year, thus labelling 2019's data as "This Year"
• Knock-on effect is that 2018 becomes the "Last Year" value as well

If any clarification or the dummy pbix file is required, please just let me know what you need (or where to upload the pbix to)

Thanks in advance,

Zane

[v-joesh-msft · Accepted solution]

Hi @Anonymous ,

Whether the field "Fin Year" is a date type, if so, try replacing dt_Example[Fin Year] with dt_Example[Fin Year].Year or you can try the following measure:

Revenue Last Year =
VAR __maxyear =
    CALCULATE ( MAX ( dt_Example[Fin Year] ), ALLSELECTED ( dt_Example[Fin Year] ) )
RETURN
    CALCULATE (
        [Total Revenue],
        FILTER ( ALL ( dt_Example[Fin Year] ), dt_Example[Fin Year] = __maxyear - 1 )
    )

-- and "This Year" is identical except without the – 1

Here is a demo, please try it：https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EfBDCAIS2JdJoyTrqbhtOuQBph12pEqzdb0p57qXMWXJoQ?e=bZSlRo

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks Joey

I went with the VAR solution in this instance

Appreciate the help!