Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Annette
Regular Visitor

YoY% change with Company Fiscal Year

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 YearFiscal QuarterSeller CountryBuyer CountryDistiPartnerSell Through US$
2018FY18 Q1AUSTRALIAAUSTRALIAABCXYZ$1,000,000
2018FY18 Q2AUSTRALIAAUSTRALIAABCXYZ$200
2018FY18 Q3AUSTRALIAAUSTRALIAABCXYZ$450,000
2018FY18 Q4AUSTRALIAAUSTRALIAABCXYZ$23,000
2019FY19 Q1AUSTRALIAAUSTRALIAABCXYZ$100
2019FY19 Q2AUSTRALIAAUSTRALIAABCXYZ$200,000
2019FY19 Q3AUSTRALIAAUSTRALIAABCXYZ$345,000
2019FY19 Q4AUSTRALIAAUSTRALIAABCXYZ$123,000

 

Regards, Annette.

1 REPLY 1
amitchandak
Super User
Super User

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

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.