how to create a column with multiple criteria

[AleFVG]

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.

[AleFVG]

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)

• Item field:  In this field there is an article code that can be repeated several times during the year (currently there are about 13.000 different codes)
• Total Value
• Quantity
• Month
• AVG Price unit: i added column calculated with dax= Total Value / quantity
• AVG Price unit last month: this is the column I want to add. I would like that, if a code was purchased in the last month, in this column where that code is present, the avg price unit referring to the last month is inserted. If a code has not been purchased in the last month, avg unit of all the rows where the code is present must be entered
  

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

I hope I was clear,

Thanks for your help!

[az38]

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)

[amitchandak]

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