cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jcpewittyancey Frequent Visitor
Frequent Visitor

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
jcpewittyancey Frequent Visitor
Frequent Visitor

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
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


jcpewittyancey Frequent Visitor
Frequent Visitor

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.

Community Support Team
Community Support Team

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

@jcpewittyancey,

 

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
jcpewittyancey Frequent Visitor
Frequent Visitor

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