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
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.
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
108 | |
102 | |
94 | |
71 |
User | Count |
---|---|
173 | |
134 | |
132 | |
102 | |
95 |