Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
omariobross
New Member

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.
HELP.png

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

 

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

 

Thanks a lot!! 🙂

3 REPLIES 3
amitchandak
Super User
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?

 

@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/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors