DAX: MAX value for Item if value is 0

[Anonymous]

Hi everyone,

I'm trying to create a new column that will return the MAX value for an item in a particular Month & Year if the value is 0.

If the value is >0, it should return the same value.

In my case the value is the "Selling Price". Ideally, it should return the new column "Revised Selling Price for the Month".

Thank you all!

[Jihwan_Kim · Accepted solution]

Revised SellingPrice CC =

VAR _currentitem = Data[Item]

VAR _currentmonth = Data[Month]

VAR _currentyear = Data[Year]

VAR _maxprice =

MAXX (

FILTER (

Data,

Data[Item] = _currentitem

&& Data[Month] = _currentmonth

&& Data[Year] = _currentyear

),

Data[SellingPrice]

)

RETURN

IF ( Data[SellingPrice] <> 0, Data[SellingPrice], _maxprice )

Link to the sample PBIX file 

[Anonymous]

It works wonderfully thanks a lot!!

[amitchandak]

@Anonymous , Try a new column like

measure =
var _1 = maxx(filter(Table, [Month] =earlier([Month]) && [year] = earlier([year])), [selling price])
return
if([selling price] =0, _1, [selling price])