Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jengwt
Helper V
Helper V

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

3 REPLIES 3
Anonymous
Not applicable

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

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

Hi, folks. I just noticed this post after a long time. So it looks like I did not end up using the above DAX. We used some more hard code because of some special cicumstances we had to factor in. I have learned a lot about Power BI in the mean time, and I would not recommend using either method.

Be very careful about default datetime math functions in PBI, I have found they tend to do illogical things.

For instance, we had a guy with an account which hadn't produced any negative events for something like a year. We originally had coded a rate of negative events measure to use the MTD and YTD functions. In the absence of recent data, these functions actually went back a year and used the last month in which his account recorded a negative event as the starting point for those calculations. We had to fix that by writing our own YTD and MTD calculations.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.