How to calculate QOQ change % on year

[BhavyaM]

I have sales column with date column (2019Q1,2019Q2,2019Q3,2019Q4,2020 Q1)

Here In Table chart I need to show QoQ change percentage on Year basis like for ex: 

2019Q1 % will be (2020Q1/ 2019Q1)-1

2019Q2 % will be (2020Q1/2019q2)-1

2019Q3 % will be (2020q1/20219q3)-1

Please help me how to achieve this

[v-xuding-msft · Accepted solution]

Hi @BhavyaM ,

I create a simple sample. Please reference it to have a try.

Calendar =
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) ),
    "Year", YEAR ( [Date] ),
    "Qtr", QUARTER ( [Date] )
)

% = 
VAR MaxYear =
    MAX ( 'Calendar'[Year] )
VAR MaxQtr =
    CALCULATE (
        MAX ( 'Table'[Qtr] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year] = MaxYear )
    )
VAR ThisQtr =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Qtr] = MaxQtr && 'Table'[Year] = MaxYear )
    )
VAR LastQtr =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( 'Table', 'Table'[Year] = MaxYear - 1 )
    )   
RETURN
    DIVIDE ( ThisQtr - LastQtr, LastQtr )

For more details, please see the attachment.

[mahoneypat]

Do you have a Date table in your model?  If so, you can make a column that has the same YearQuarter syntax and have a relationship to your existing YearQuarter column, and you could use Time Intelligence DAX to do you calculatation.  If not, you could add a column in the query editor (or with DAX as a calculated column) that has the last date of each quarter.  Then you can write DAX expressions with variables to get the current value and previous quarter value, and do you calculation to get the QoQ change.

If you are not familiar with how to do that, you can post some sample/mock data and the community will likely respond with the exact formula you need.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat