The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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:
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 |
Solved! Go to 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.
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.
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
User | Count |
---|---|
15 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
23 | |
14 | |
13 | |
8 | |
8 |