Forum Discussion

omariobross's avatar
omariobross
New Member
5 years ago

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 ...
average of a measure
Help Requested
Quarter over Quarter
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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's avatar
omariobross
New Member
5 years ago

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's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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/