YOY and by QTR Growth %

[Anonymous]

Hello

I wanted to create a calculated field that can show both the breakdown of the year-to-year growth percentage and the qtr growth rate. The following is a sample panel for reference.

Link

Data:

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

Thank you in advance to anyone who can help!!.

[amitchandak]

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-Y...

[Anonymous]

Appreciate for your recommendation and  suggesting me on intelligence function👍

[Ashish_Mathur]

Hi,

There must be a Month column in your source data.  That column can be the first or the last month of the quarter.  Also, please clarify the months which fall in Q1.

[Anonymous]

Hi,

Thanks for the response.

In my scenario, I don't have the exact dates for the bookings but Fiscal Q1 starts in August and ends in July for each fiscal year. 

I have added staring month for each Qtr below:

Bookings	Month	QTR	Year
1000	MAY	Q4FY18	2018
2000	FEB	Q3FY19	2019
10000	AUG	Q1FY20	2020
25000	FEB	Q3FY18	2018
6000	NOV	Q2FY19	2019
50000	NOV	Q2FY20	2020

[Ashish_Mathur]

Hi,

This is what i think you want.  Download the PBI file from here.

Hope this helps.

[v-lili6-msft]

hi @Anonymous 

For custom YOY, you could create a measure as below:

YOY_Last Year = CALCULATE([NET],FILTER(ALL(RETURNS[Year]),RETURNS[Year]=MAX(RETURNS[Year])-1))

If not your case, Please share your expected output for your sample data.

Regards,

Lin

[Anonymous]

Thanks for the Response. Actually I want to break down the growth by year and Qtr. Here's link PBI

[v-lili6-msft]

hi  @Anonymous 

One question: In this sample, what is your expected output for [YOY_Last Year]

Regards,

Lin

[nandukrishnavs]

@Private

Because this is a time-smart calculation, you need a date column in the table.

Sample table

date	reserves
What's 01 January 2018	1230
What's 01 February 2018	1200
March 1 2018	1170
April 1 2018	1140
May 01 2018	1110
What's 01 June 2018	1080
What's 01 July 2018	1050
What's 01 August 2018	1020
What's 01 September 2018	1110
What's 01 October 2018	1200
What's 01 November 2018	1290
What's 01 December 2018	1380
01 January 2019	1470
01 February 2019	1560
March 01 2019	1650
April 01 2019	1740
May 01 2019	1050
June 01 2019	1020
01 July 2019	990
August 01 2019	960
01 September 2019	930
01 October 2019	900
01 November 2019	870
December 01 2019	840
01 January 2020	810
01 February 2020	1380
01 March 2020	1470
April 01, 2020	1560

QOQ% = 
VAR thisQTR =
    SUM ( 'Table'[Reservations])
VAR lastQTR =
    CALCULATE (
        SUM ('Table'[Reservations] ),
        DATESINPERIOD ( 'Table'[Date].[ Date], MIN ( 'Table'[Date] ), -1, QUARTER )
    )
RETURN
    DIVIDE ( thisQTR - lastQTR, lastQTR, 0 )

YOY% = 
VAR thisYear =
    SUM ( 'Table'[Reservations] )
VAR lastYear =
    CALCULATE ( SUM ( 'Table'[Reservations] ), SAMEPERIODLASTYEAR ( 'Table'[Date].[ Date] ) )
RETURN
    DIVIDE ( thisYear - lastYear, lastYear, 0 )

exit

Did I answer your question? Mark my position as a solution!
Appreciate with a kudos🙂

[Anonymous]

Appreciate for your suggestions 👍