Year on Year Weighted Average variance

[billybazinga]

Hi everyone,

I am hoping to get some pointers as I'm lost as to where to start with a DAX formula to calculate a financial year on year weighted average movement (as a %).

In essence, I would like to create a measure to track how much the price of an item, by vendor, has moved from the previous financial year (01-July 2020 to 30-June 2021) to the current financial year (01-July 2021 to 30-June 2022). The intent being that based on the visual, this could agregatte at a vendor level to show price variances through time (ie a vendor went up by 10% year on year).

My main challenge is:

• I would like to capture this calculation as a weighted average (taking into account the quantity ordered)
• Only include repeat purchases (ie excluding all items ordered only once in a period, which can use the order# column as a distinction
• Allow the calc at item by vendor level as an item can theoritically be provided by multiple vendors.

Below is a very simplified version of my dataset

Thanks in advance for any pointers.

OF

Vendor	Date	Order#	Item	Qty	Price
A	Jan-20	1	123	20	$10
A	Apr-20	2	123	40	$ 10
A	Sep-21	3	123	50	$ 15
A	Jan-22	4	123	90	$ 15
A	Mar-22	5	123	40	$ 15
A	Jan-20	6	456	500	$ 15
A	Apr-20	7	456	300	$ 16
A	Sep-21	8	456	200	$ 30
A	Jan-22	9	456	100	$ 30
A	Mar-22	10	456	10	$ 30
B	Feb-20	11	120	50	$ 50
B	Sep-21	12	120	60	$ 100
B	Dec-21	13	120	10	$ 120
C	Jun-20	14	456	30	$ 12
C	Jun-20	15	456	40	$ 12
C	Aug-21	16	456	1	$ 19
C	Jun-21	17	456	30	$ 40
C	Jun-21	18	456	5	$ 40

[v-zhangti · Accepted solution]

Hi, @billybazinga 

You can try the following methods.
Date Table:

Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

Start of the year = 
IF(MONTH('Date'[Date]) >=7,Date(YEAR('Date'[Date]),7,1),Date(YEAR('Date'[Date])-1,7,1))

Financial year = YEAR([Start of the year])

Measure:

Aver = DIVIDE(SUMX('Table',[Price]*[Qty]),SUMX('Table',[Price]))

Pre aver = CALCULATE(DIVIDE(SUMX('Table',[Price]*[Qty]),SUMX('Table',[Price])),
FILTER(ALL('Date'),[Financial year]=SELECTEDVALUE('Date'[Financial year])-1))

Measure = [Aver]-[Pre aver]

Is this the output you expect? If not, please provide more details, especially what you expect the output to be.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@billybazinga ,

Wt Avg = divide(Sumx(Table, [Qty]*[Price]), sum(Table[Qty]) )

Wt Avg last year= calculate( divide(Sumx(Table, [Qty]*[Price]), sum(Table[Qty]) ) , dateadd('Date'[date],-1, Year) )

Using a date table joined with your table

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA