Forum Discussion
HELP - Average over QoQ% quick measure
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!! 🙂
3 Replies
- amitchandak
Super User
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))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/
