HELP - Average over QoQ% quick measure

[omariobross]

Hi all! 🙂

I've just calculated, using a quick measure method, a Quarter over Quarter percentage change. If it's true that I got the desire result as is showing on the following table , I'd like the average percentage change by year and the total (for all the years).

For example: the average of Q2 (Trim 2 in spanish), Q3 & Q4 for the year 2018 (being the data 3,50%, 3,99% and -55,11% respectively) is -15,87% and not -13,35% as is noted on the total summarizing.

In the following image, I show you how is that I've performed the quick measure.

P. S. I've changed the SUM of value by Average, MIN, MAX, etc... but the issue stills 😞

Thanks a lot!! 🙂

[amitchandak]

@omariobross , You can get this Qtr vs last qtr using time intelligence

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))

Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))

diff = [QTD Sales]-[Last QTD Sales]
diff % = divide([QTD Sales]-[Last QTD Sales],[QTD Sales])

for non standard qtr, create a rank on qtr start date or YYYYQQ

Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

Then create column like
This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

[omariobross]

Hi @amitchandak , thanks a lot!

Only an additional question please:
What should I consider additionally if I want the diff% that consolidate all the quarters for one year, and not the last one?

[amitchandak]

@omariobross , I think you taking about subtotal. At year level it might use only last qtr

Create YTD diff in same manner

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date])))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,QUARTER)))

use isinscope to switch the measure

if(isinscope(Date[Year]) && not(isinscope(Date[Qtr])) , [YTD Diff%], [QTD diff%])

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/