cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jengwt Member
Member

YoY Calculations in Matrix with Year Columns

Hello. I have some a table of monthly sales data that I am trying to put into a matrix to display year over year growth per business unit, by year. This matrix is set up with Rows = Business Unit, Columns = Year (filter, top 6), and Values = Measure. (see scrnsht below for what I want it to look like)

I am trying to build a Measure that will dynamically calculate the YoY growth % for a given year, but the row filtering by year seems to be confusing my code.

The following is the current iteration of my code which does yield values, albeit incorrect ones:

 

YoY_Growth = (  CALCULATE(SUM([REVENUE]), FILTER('TABLE', '[YEAR]))  /  CALCULATE(SUM([REVENUE]) , FILTER(ALL('TABLE') , [YEAR] - 1))  ) - 1

 

Basically I need to tell the measure to look at whatever the year is the of the column that the cell is in, and then take the sum of the revenue for that year divided by the sum of the prior year's revenue. It has to be dynamic so that I will not need to hard code any year numbers.

 

Thanks for your help!

 

DesiredTable.PNG

2 REPLIES 2
Anonymous
Not applicable

Re: YoY Calculations in Matrix with Year Columns

Hi @jengwt,

 

You could try the quick measure, right click on your column name, you will see an option to create a New Quick Measure. Refer to the screenshot below.

Screenshot_2-22.png

 

 

 

You may have to mark your date table as Custom Date table with no duplicate values.

 

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-february-2018-feature-summary/#customDateT...

jengwt Member
Member

Re: YoY Calculations in Matrix with Year Columns

Silly of me that I didn't notice that before. However, that  doesn't work either.

 

I beleive it has something to do with how the dates work. You see, these numbers come from monthly reports, and are not detailed at the date level. Originally, the data table just had a rolling count of the months (since the year when this report was produced) as a number, and the year of the record, as a number. From these two, I inserted a date column as the first day of the coresponding month and year, formatted as a date. These first-day-of-month dates are unique and stored in the calendar table, and are related to the coresponding month number. Please see the poorly rendered illustration below.

 

If I try to use the YoY Quick Measure, it complains, "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."

 

Here is the code from the quick measure. I am going to try playing with it to see if I can adapt it to work with what I have.

 

YoY% =
IF(
 ISFILTERED('CALENDAR'[DATE]),
 ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
 VAR __PREV_YEAR =
  CALCULATE(
   SUM('DATA'[REVENUE]),
   DATEADD('CALENDAR'[DATE].[YEAR], -1, YEAR)
  )
 RETURN
  DIVIDE(
   SUM('DATA'[REVENUE]) - __PREV_YEAR,
   __PREV_YEAR
  )
)

 

TableRelation.png