Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculated Column inside Matrix where measures are rows

 Hi, I have a list of many Measures by Year, in the Matrix, I had the Values "show on rows" so that the measures are in rows, and the Years are Columns.  Now I want to take YOY comparisons of the Yea...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

hi, Anonymous

You can use SAMEPERIODLASTYEAR Function to add a last year total measure like below:

last year total = CALCULATE(SUM(Table1[Sales]),SAMEPERIODLASTYEAR('Date'[Date]))

And YOY % increase measure

YOY% = DIVIDE([total]-[last year total],[last year total])

Also you can use New quick measure to quickly add a measure

then drag these measure into Value

Result:

 

Best Regards,

Lin

 

 

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Lin,

     

    Yes, I got that to work just fine when I have measures totaling each year (2015-2018) and YOY changes in dollars and %'s for P&L & Sales items (coming off one column of values in the data coming from Excel).  But now I am doing another chart where I am taking KPI measures/functions off that same P&L data, then showing those functions as rows (2015-2018) (through the values feature "show on rows") and now I want calculated columns for YOY changes going across, on those measures.  I think my issue is that if I have 20 KPI Measures/DAX Formulas, and then I put them into rows, it seems I have to create measures for each year on those measures for each YOY Changes (2016-2018),  What I did in the meantime was create a ton of measures, then make multiple Matrixes for each YOY Change and then sort of hide the row headings and line all the tables up.  I know I could use cards instead, but that would slow down the dashboard.  I know this sounds confusing.... I will try what you wrote below, I just don't think it's going to work, but I will let you know!