QOQ Calculation

[Himanshu_1306]

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 )

[amitchandak]

@Himanshu_1306 , 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-Calend...
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5e...
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

[Himanshu_1306]

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()))