Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

QOQ Calculation

Hi All,

 

I have a scenario where i want to show % increase/decrease in my data as of QOQ%. I want to show data from Current day Current Quarter to Current Day last Quarter. 

How should this be approached?

 

I was using the below formula to compare this quarter V/s Last quarter earlier but now business wants to see numbers differently. Like if today is 12th Dec Q4 so I want to have a comparion between Today and same day (Of the quarter) Q3.

 

P.s: We follow month end approach so below are my Quarter scenarios:

 

Q1 -> Feb-April 

Q2 ->May-Jul

Q3 ->Aug - Oct

Q4 ->Nov-Jan

 

Previous FOrmula Used:

 

QOQ% =
VAR thisQ =
[QTD ACV]
VAR lastQ =
CALCULATE (
[ACV],
DATESBETWEEN(ACV[CLOSE_DATE],"08-01-2022","10-31-2022")
)
RETURN
DIVIDE ( thisQ - lastQ, lastQ, 0 )

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous , Create a calendar like

     

    Calendar =
    var _tab = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
    , "Month Year", format([date],"mmm-yyyy")
    , "Month year sort", year([date])*100 + month([date])
    , "Month",FORMAT([Date],"mmmm")
    , "Month sort", month([DAte])
    , "Qtr Start Date", var _rem = mod(month([Date]),3)
    return eomonth([Date], Switch(_rem,2,-1, 0,-2, 1,-3))+1

    )
    return
    Addcolumns(_tab,
    "Qtr Rank", rankx(_tab,[Qtr Start Date], ,asc,dense),
    "Qtr Day", datediff([Date],[Qtr Start Date], day)+1
    )

     

     

    then have measures like

     

    This Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
    Last Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))
    QTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank]) && [Qtr Day] <=max([Qtr Day])))
    LQTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1 && [Qtr Day] <=max([Qtr Day])))

     

     

     

    for full calendar

    Creating Financial Calendar - From Any Month
    https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441
    https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5ee47de6a208
    https://amitchandak.medium.com/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1

     

    also refer

    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Amit,

       

      This did not resolve my issue.

       

      I have created a measure and hard coded the values I need but not sure how to make it dynamic.

      For last quarter I need to pick the same day as current quarter and then do the comparison.

       

      Measure Created:

       

      QOQ% =
      VAR thisQ =
          [QTD ACV]
      VAR lastQ =
          CALCULATE (
              [ACV],
              DATESBETWEEN(ACV[CLOSE_DATE],"09-13-2022","10-31-2022")
          )
      RETURN
          DIVIDE ( thisQ - lastQ, lastQ, 0 )
       
      QTD ACV = CALCULATE([ACV],DATESBETWEEN(ACV[CLOSE_DATE],"11-01-2022",TODAY()))