Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there!
I need help calculating QOQ (quarter over quarter) difference with multiple variables as part of the data. Here is an example of data:
I greatly apprecaite any help you can give!
FABRIC | YEAR | QTR | QTR_DATE | AVG_QTR_PRICE | |||||
cotton | 2020 | Q1 | 1/1/20 | 83.56 | |||||
cotton | 2019 | Q4 | 10/1/19 | 84.00 | |||||
cotton | 2019 | Q3 | 7/1/19 | 86.89 | |||||
cotton | 2019 | Q2 | 4/1/19 | 88.56 | |||||
cotton | 2019 | Q1 | 1/1/19 | 89.15 | |||||
nylon | 2020 | Q1 | 1/1/20 | 55.64 | |||||
nylon | 2019 | Q4 | 10/1/19 | 56.52 | |||||
nylon | 2019 | Q3 | 7/1/19 | 57.58 | |||||
nylon | 2019 | Q2 | 4/1/19 | 58.64 | |||||
nylon | 2019 | Q1 | 1/1/19 | 59.57 | |||||
linen | 2020 | Q1 | 1/1/20 | 67.89 | |||||
linen | 2019 | Q4 | 10/1/19 | 68.56 | |||||
linen | 2019 | Q3 | 7/1/19 | 66.89 | |||||
linen | 2019 | Q2 | 4/1/19 | 66.25 | |||||
linen | 2019 | Q1 | 1/1/19 | 66.12 |
Hi @Anonymous
If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly.thanks!
@Anonymous , please find attached solution after signature
Appreciate your Kudos.
Make sure you have date calendar and try like this
QTD Sales = CALCULATE(Average(Sales[Price]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(Average(Sales[Price]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last to last QTD Sales = CALCULATE(Average(Sales[Price]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(Average(Sales[Price]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
Last year same QTD Sales = CALCULATE(Average(Sales[Price]),DATESQTD(dateadd('Date'[Date],-1,Year)))
trailing QTR = CALCULATE(Average(Sales[Price]),dateadd('Date'[Date],-1,QUARTER))
trailing 4 QTR = CALCULATE(Average(Sales[Price]),dateadd('Date'[Date],-4,QUARTER))
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/
Does the above reply helps. if you need more help make me @
Appreciate your Kudos.
Hi,
Please show clearly the end result that you are expecting.
something like this (QoQ on bottom row)
ignore these values i know it is different to the data i provided
Q4 2017 | Q1 2018 | Q2 2018 | Q3 2018 | Q4 2018 | Q1 2019 | Q2 2019 | Q3 2019 | Q4 2019 | Q1 2020 |
55.48249 | 61.55467 | 67.55078 | 70.3638 | 59.48667 | 54.82667 | 59.78333 | 56.49417 | 57.5525 | 54.56167 |
10.9% | 9.7% | 4.2% | -15.5% | -7.8% | 9.0% | -5.5% | 1.9% | -5.2% |
Hi,
You may download my PBI file from here.
Hope this helps.
@Anonymous
This diff between QTD and last QTD or the % of that. You can find the formulas given above.
If you looking for a display. In matrix visual, you have an option show on the row. That will move your measures in the first column. and now move your QTR name to cols.
the difference I am looking for is % difference from one QTR to the next i.e. Q1 2020 % difference from Q4 2019, Q4 2019 % difference from Q3 2019 and so on
User | Count |
---|---|
88 | |
73 | |
69 | |
64 | |
56 |
User | Count |
---|---|
98 | |
92 | |
84 | |
74 | |
66 |