Power Bi matrix table measure, how to find YoY

[Anonymous]

I'd like help creating a measure that can find the Year on year (YoY) for the 5 values in my matrix table: Count of weeks, sum of recipients, sum of engaged, engagement rate, and sum of revenue.

The data is dynamic so will be added to each week, this means the YoY measure needs to also be dynamic.

This is a copy of my data: https://drive.google.com/file/d/13jZ_9t-XjqM_wdPectPAFbgJpX6Q3g0H/view?usp=sharing

To find YoY, I know I need to write a measure that calculates ((latest week/ year before latest week) - 1). I have written a calculated column, Position that finds the latest week = 1, year before latest week = 2, and else = 0.

How do I write a measure that I can use in my table to find the YoY for all my values? Any help would be appreciated

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You may use these measures:

YOY Recipients = DIVIDE(CALCULATE(SUM(TestData[Recipients]),TestData[Position]=1),CALCULATE(SUM(TestData[Recipients]),TestData[Position]=2))-1

YOY Engaged = DIVIDE(CALCULATE(SUM(TestData[Engaged]),TestData[Position]=1),CALCULATE(SUM(TestData[Engaged]),TestData[Position]=2))-1

YOY Engagment rate = DIVIDE(CALCULATE([Engagment rate],TestData[Position]=1),CALCULATE([Engagment rate],TestData[Position]=2))-1

YOY Revenue = DIVIDE(CALCULATE(SUM(TestData[Revenue]),TestData[Position]=1),CALCULATE(SUM(TestData[Revenue]),TestData[Position]=2))-1 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Thank you so much, I wouldn't have thought of doing it that way. Have a great day!