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

[jcpewittyancey]

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.

[jcpewittyancey · Accepted solution]

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

[Greg_Deckler]

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

[jcpewittyancey]

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.

[v-chuncz-msft]

@jcpewittyancey,

You may take a look at the following article.

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