YoY% change with Company Fiscal Year

[Annette]

Hello Team,

Please help provide your guidance on how to create a simple measure that calculates the YoY% and QoQ% Sell Through.

I tried the quick measure for year over year but it does not show me the correct information.

I would like to use this YoY% and QoQ% measure to create a matrix visual and bar/line chart to show the YoY% and QoQ% changes. The company's fiscal year begins in Nov each year. For example, FY19 Q1 = 01-Nov-2018 to 31-Jan-2019

Here below shows a sample of my raw data source that I have already fed into my Power BI dashboard/desktop. 

Your great help would be most appreciated! Thank you.

Fiscal Year	Fiscal Quarter	Seller Country	Buyer Country	Disti	Partner	Sell Through US$
2018	FY18 Q1	AUSTRALIA	AUSTRALIA	ABC	XYZ	$1,000,000
2018	FY18 Q2	AUSTRALIA	AUSTRALIA	ABC	XYZ	$200
2018	FY18 Q3	AUSTRALIA	AUSTRALIA	ABC	XYZ	$450,000
2018	FY18 Q4	AUSTRALIA	AUSTRALIA	ABC	XYZ	$23,000
2019	FY19 Q1	AUSTRALIA	AUSTRALIA	ABC	XYZ	$100
2019	FY19 Q2	AUSTRALIA	AUSTRALIA	ABC	XYZ	$200,000
2019	FY19 Q3	AUSTRALIA	AUSTRALIA	ABC	XYZ	$345,000
2019	FY19 Q4	AUSTRALIA	AUSTRALIA	ABC	XYZ	$123,000

Regards, Annette.

[amitchandak]

Create a date table and move all your fiscal year(FY, FQ etc) calculations

YTD you can use time intelligence. For QTD create an overall rank on Qtr use that

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

//New column in date 
Qtr Rank =Rank.x(all('Date'),'Date'[Fiscal Quarter])

Last Qtr = CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Qtr Rank ]=(Max('Date'[Qtr Rank ])-1))

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/

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin