cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
zane_d Frequent Visitor
Frequent Visitor

Matrix Troubles - Year on Year Column Interaction

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:

Untitled.png

 

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

Capture.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-joesh-msft Established Member
Established Member

Re: Matrix Troubles - Year on Year Column Interaction

Hi @zane_d ,

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/EfBDCAIS2JdJoyTrqb...

 

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.

View solution in original post

2 REPLIES 2
v-joesh-msft Established Member
Established Member

Re: Matrix Troubles - Year on Year Column Interaction

Hi @zane_d ,

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/EfBDCAIS2JdJoyTrqb...

 

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.

View solution in original post

zane_d Frequent Visitor
Frequent Visitor

Re: Matrix Troubles - Year on Year Column Interaction

Thanks Joey

 

I went with the VAR solution in this instance

 

Appreciate the help!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 333 members 3,282 guests
Please welcome our newest community members: