QOQ difference

[Anonymous]

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

[v-diye-msft]

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!

[amitchandak]

@Anonymous , please find attached solution after signature

Appreciate your Kudos.

[amitchandak]

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.

[Ashish_Mathur]

Hi,

Please show clearly the end result that you are expecting.

[Anonymous]

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%

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak]

@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.

[Anonymous]

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