Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello to everyone!
have create a Query with 5 columns, the column names are "Item", "Desc","Month", "Qty" and "Avg price unit". The structure is like this:
Item | Desc | Month | Qty | Avg price unit |
L1111 | AA | 1 | 4 | 3 |
L2222 | BB | 1 | 6 | 6 |
L3333 | CC | 2 | 6 | 8 |
L4444 | DD | 3 | 10 | 3 |
L1111 | AA | 4 | 10 | 2,5 |
L2222 | BB | 3 | 4 | 6 |
L8888 | XX | 2 | 4 | 0,5 |
L2222 | BB | 4 | 3 | 4 |
L2222 | BB | 4 | 7 | 4 |
L5555 | EE | 1 | 1 | 10 |
What I would like to do is to add another column into this query, The sixth column is named "Avg unit price last month". I would like to have this column to always display the avg price only only of the most recent month available.
Like this:
Item | Desc | Month | Qty | Avg price unit | Avg unit last month |
L1111 | AA | 1 | 4 | 3 | 2,5 |
L2222 | BB | 1 | 6 | 6 | 15 |
L3333 | CC | 2 | 6 | 8 | 8 |
L4444 | DD | 3 | 10 | 3 | 3 |
L1111 | AA | 4 | 10 | 2,5 | 2,5 |
L2222 | BB | 3 | 4 | 15 | 15 |
L8888 | XX | 2 | 4 | 0,5 | 0,5 |
L2222 | BB | 4 | 3 | 15 | 15 |
L2222 | BB | 4 | 7 | 15 | 15 |
L5555 | EE | 1 | 1 | 10 | 10 |
Thanks in advance.
Hello!
First of all, thanks for your support!
I try to give a better explanation.
Have a create the following table. This table only contains data for the current year (2020)
Check Example in the table:
In this case the last month on the table is 5 (May), The only item that has the price in month 5 (May) is the Item L11 and the avg price unit is 1,80$. Therefore, in all the rows where the L11 code is present in the AVG PRICE LAST MONTH column the price will be entered is 1,80$.
For the items L22 and L33 no purchase was made in month 5 (May) then in the column AVG PRICE LAST MONTH the average price (sum total value for Item L22 / sum quantity for item L22) must be entered in all the lines where the codes are present. In the table for L22 the avg price is 3,13$ (sum total value for Item L22 / sum quantity for item L22) and the same for the item L33 the avg is 3,49$.
Hi @AleFVG
what if you will have a few years in the table? and why does
L2222 | BB |
has 15 in your column?
anyway, try column
Avg unit last month =
var _lastMonth = CALCULATE(MAX('Table'[Month]), ALLEXCEPT('Table', 'Table'[Item]))
RETURN
CALCULATE(MAX('Table'[Avg price unit]), ALLEXCEPT('Table', 'Table'[Item]), 'Table'[Month]=_lastMonth)
@AleFVG , Try both as new columns
last month = maxx(filter(Table, [Item]=earlier([Item]) && [Month]<earlier([Month])),[Month])
Avg unit last month =maxx(filter(Table, [Item]=earlier([Item]) && [Month]<earlier([last month ])),[ Avg price unit])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |