YOY and by QTR Growth %

[Anonymous]

Hello,

I wanted to create a calculated field that can show both  Year over Year and breakdown by Qtr growth %. Below is a sample dashboard for reference.

Link

Data:

Bookings	QTR	Year
1000	Q4FY18	2018
2000	Q3FY19	2019
10000	Q1FY20	2020
25000	Q3FY18	2018
6000	Q2FY19	2019
50000	Q2FY20	2020

Thanks in advance to anyone who can assist!!.

[v-shex-msft · Accepted solution]

HI @Anonymous,

You can add a calculated column to extract year and quarter from 'QTR' field then use the following measure formula to calculate the growth% based on current aggregate row content level:

Calculate column:

YQ =
RIGHT ( 'Table'[QTR], 2 ) * 10
    + RIGHT ( LEFT ( 'Table'[QTR], 2 ), 1 )

Measure formula:

Measure = 
VAR firstYQ =
    MINX ( ALLSELECTED ( 'Table' ), [YQ] )
VAR currYQ =
    MAX ( 'Table'[YQ] )
VAR prevYQ =
    CALCULATE (
        MAX ( 'Table'[YQ] ),
        FILTER ( ALLSELECTED ( 'Table' ), [YQ] < currYQ )
    )
VAR cyBooking =
    CALCULATE (
        SUM ( 'Table'[Bookings] ),
        FILTER ( ALLSELECTED ( 'Table' ), INT ( [YQ] / 10 ) = INT ( currYQ / 10 ) )
    )
VAR pyBooking =
    CALCULATE (
        SUM ( 'Table'[Bookings] ),
        FILTER ( ALLSELECTED ( 'Table' ), INT ( [YQ] / 10 ) = INT ( currYQ / 10 ) - 1 )
    )
VAR cyqBooking =
    CALCULATE (
        SUM ( 'Table'[Bookings] ),
        FILTER ( ALLSELECTED ( 'Table' ), [YQ] = currYQ )
    )
VAR pyqBooking =
    CALCULATE (
        SUM ( 'Table'[Bookings] ),
        FILTER ( ALLSELECTED ( 'Table' ), [YQ] = prevYQ )
    )
RETURN
    IF (
        prevYQ <> BLANK (),
        IF (
            COUNTROWS ( VALUES ( 'Table'[YQ] ) ) > 1,
            IF ( prevYQ <> firstYQ, DIVIDE ( cyBooking - pyBooking, pyBooking, -1 ), 1 ),//year level
            DIVIDE ( cyqBooking - pyqBooking, pyqBooking, -1 )//year quarter level
        ),
        1
    )

Notice: I also add a condition to check if the current data label is the first one and replace it with 100%.

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , In case you are using Date then use time intelligence

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD((Table[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd(Table[Date],-1,QUARTER)))
CALCULATE([Total Value], PREVIOUSQUARTER('Calendar'[Date]))
Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd(Table[Date],-1,QUARTER))))

Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd(Table[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd(Table[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd(Table[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd(Table[Date],-1,Year))))

trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-1,QUARTER))
trailing  4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-4,QUARTER))



YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((Table[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR(Table[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd(Table[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd(Table[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd(Table[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-1,Year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

In case you do not have Date use Rank .

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

This Qtr= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[QtrRank]=max('Date'[QtrRank])))
Last Qtr= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[QtrRank]=max('Date'[QtrRank])-1))

Last Qtr last year= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[QtrRank]=max('Date'[QtrRank])-4))

Refer to my Webinar:https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

[mahoneypat]

From the data provided, the YoY pct change measure is straight forward, and one way to do it is shown below.

Pct Change Y = var currentyearsum = SUM(Bookings[Bookings])

var prevyear = MAX(Bookings[Year])-1

var prevyearbookings = CALCULATE(SUM(Bookings[Bookings]), Bookings[Year]=prevyear)

return (currentyearsum-prevyearbookings)/prevyearbookings

However, to do the QoQ calculation, you would need to be able to increment to the previous quarter as with year.  To do that you could add a Date table to your model that has a column with which you can do that (or add a column to this table).  Once that is in place, a measure could be written that recognizes the scope (Y or Quarter on X axis), so that the right result is returned.

Regards,

Pat