Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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!!.
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...
Appreciate for your recommendation and suggesting me on intelligence function👍
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.
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 |
Hi,
This is what i think you want. Download the PBI file from here.
Hope this helps.
hi @Anonymous
For custom YOY, you could create a measure as below:
If not your case, Please share your expected output for your sample data.
Regards,
Lin
Thanks for the Response. Actually I want to break down the growth by year and Qtr. Here's link PBI
hi @Anonymous
One question: In this sample, what is your expected output for [YOY_Last Year]
Regards,
Lin
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🙂
Appreciate for your suggestions 👍
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |