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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
billybazinga
Frequent Visitor

Year on Year Weighted Average variance

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

VendorDateOrder#ItemQtyPrice
AJan-20112320 $10
AApr-20212340 $ 10
ASep-21312350 $ 15
AJan-22412390 $ 15
AMar-22512340 $ 15
AJan-206456500 $ 15
AApr-207456300 $ 16
ASep-218456200 $ 30
AJan-229456100 $ 30
AMar-221045610 $ 30
BFeb-201112050 $ 50
BSep-211212060 $ 100
BDec-211312010 $ 120
CJun-201445630 $ 12
CJun-201545640 $ 12
CAug-21164561 $ 19
CJun-211745630 $ 40
CJun-21184565 $ 40

 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

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])

vzhangti_0-1656668097691.png

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]

vzhangti_1-1656668146726.png

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.

View solution in original post

2 REPLIES 2
v-zhangti
Community Support
Community Support

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])

vzhangti_0-1656668097691.png

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]

vzhangti_1-1656668146726.png

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
Super User
Super User

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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