cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Using YTD for YoY growth rate in "total" row of Matrix

Hello all!

I've created a matrix to display some quarterly data. It displays 1 year's worth of data at a time, broken down by quarter. It includes a calculation of a year-over-year growth rate.

When you select 2018, it only displays Q1-Q3 data, as the Q4 data won't be available until next month.

The matrix displays a 'total' row at the bottom, including the total YoY growth rate for the year. However, when 2018 is selected, the YoY growth rate in the total row is a large, negative number because it is comparing Q1-3 totals for 2018 to Q1-4 totals for 2017.

Is there a way to get the "total" row in the matrix to base the YoY growth rate on the equivalent year-to-date total from the previous year, instead of comparing it to the entire previous year? So in other words even though data is available for all 4 quarters of 2017, because data is only available for 3 quarters of 2018 the YoY growth rate in the total column would be calculated based off of the first 3 quarters of 2017.

Here is the matrix in question, in case it would be helpful.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I

## Re: Using YTD for YoY growth rate in "total" row of Matrix

I appreciate everyone's help. Here is what I ended up doing. It calculates the correct growth rate in all rows, including the total, whether or not all 4 quarters have data.

```YoY Growth =
IF(
ISFILTERED('VisitorEstimates'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
IF( NOT ISBLANK(SUM('VisitorEstimates'[Visitors])),
VAR __EndDate = CALCULATE(LASTDATE(VisitorEstimates[Date]), VisitorEstimates[Visitors] > 0)
VAR __StartDate = CALCULATE(FIRSTDATE(VisitorEstimates[Date]), VisitorEstimates[Visitors] > 0)
VAR __CurrentYear = YEAR(__EndDate)
VAR __LastYear = __CurrentYear - 1
VAR __Growth = DIVIDE(CALCULATE(SUM(VisitorEstimates[Visitors]),
DATESBETWEEN(VisitorEstimates[Date],
DATE(__CurrentYear, MONTH(__StartDate), DAY(__StartDate)),
DATE(__CurrentYear, MONTH(__EndDate), DAY(__EndDate))
)
) - CALCULATE(SUM(VisitorEstimates[Visitors]),
DATESBETWEEN(VisitorEstimates[Date].[Date],
DATE(__LastYear, MONTH(__StartDate), DAY(__StartDate)),
DATE(__LastYear, MONTH(__EndDate), DAY(__EndDate))
)
)
,
CALCULATE(SUM(VisitorEstimates[Visitors]),
DATESBETWEEN(VisitorEstimates[Date].[Date],
DATE(__LastYear, MONTH(__StartDate), DAY(__StartDate)),
DATE(__LastYear, MONTH(__EndDate), DAY(__EndDate))
)
)
)
RETURN __Growth
))```

4 REPLIES 4
Highlighted
Super User IV

## Re: Using YTD for YoY growth rate in "total" row of Matrix

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Helper I

## Re: Using YTD for YoY growth rate in "total" row of Matrix

Thanks very much, this is quite helpful. Now I just need to figure out the right way to calculate the YTD total for the previous year's data based on the amount of time for which data is available in the current year.

Highlighted
Community Support

## Re: Using YTD for YoY growth rate in "total" row of Matrix

You may take a look at the following article.

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I

## Re: Using YTD for YoY growth rate in "total" row of Matrix

I appreciate everyone's help. Here is what I ended up doing. It calculates the correct growth rate in all rows, including the total, whether or not all 4 quarters have data.

```YoY Growth =
IF(
ISFILTERED('VisitorEstimates'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
IF( NOT ISBLANK(SUM('VisitorEstimates'[Visitors])),
VAR __EndDate = CALCULATE(LASTDATE(VisitorEstimates[Date]), VisitorEstimates[Visitors] > 0)
VAR __StartDate = CALCULATE(FIRSTDATE(VisitorEstimates[Date]), VisitorEstimates[Visitors] > 0)
VAR __CurrentYear = YEAR(__EndDate)
VAR __LastYear = __CurrentYear - 1
VAR __Growth = DIVIDE(CALCULATE(SUM(VisitorEstimates[Visitors]),
DATESBETWEEN(VisitorEstimates[Date],
DATE(__CurrentYear, MONTH(__StartDate), DAY(__StartDate)),
DATE(__CurrentYear, MONTH(__EndDate), DAY(__EndDate))
)
) - CALCULATE(SUM(VisitorEstimates[Visitors]),
DATESBETWEEN(VisitorEstimates[Date].[Date],
DATE(__LastYear, MONTH(__StartDate), DAY(__StartDate)),
DATE(__LastYear, MONTH(__EndDate), DAY(__EndDate))
)
)
,
CALCULATE(SUM(VisitorEstimates[Visitors]),
DATESBETWEEN(VisitorEstimates[Date].[Date],
DATE(__LastYear, MONTH(__StartDate), DAY(__StartDate)),
DATE(__LastYear, MONTH(__EndDate), DAY(__EndDate))
)
)
)
RETURN __Growth
))```

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors