undefined

[Anonymous]

I have a dimention table as below. product code is type text, monthyear is type date, MRP is type decimal, flag is type text.

ProductCode	MonthYear	MRP	Flag
123	jan,2022	10	NO
123	feb,2022	10	NO
123	March,2022	10	NO
123	April, 2022	15	YES
121	jan,2022	12	12
121	march,2022	15	yes

In above table i need to add a calculated column as below.

ProductCode	MonthYear	MRP	Flag	End date
123	jan,2022	10	NO	30/03/2022
123	feb,2022	10	NO	30/03/2022
123	March,2022	10	NO	30/03/2022
123	April, 2022	15	YES
121	jan,2022	12	12	28/02/2022
121	march,2022	15	yes

I need to add recent date previous date as end date, when MRP got change, in to new calculated column.
It is very helpfull if some one give reply to me as soon as possible. Thanks in Advance.

[Anonymous · Accepted solution]

Hi @Anonymous 

You can try the following code:

End Date = IF([Flag]="Yes",BLANK(),MAXX(FILTER('Table','Table'[ProductCode]=EARLIER('Table'[ProductCode])&&[Flag]="Yes"),[MonthYear])-1)

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

hi @Anonymous 

do you have other date column in other format beside this one - "jan, 2022"? 

[Anonymous]

NO, I have onle one date column in this table.

[FreemanZ]

hi @Anonymous 

Just realized "jan, 2022" is converted to 1/1/2022 by Power BI automatically. If it is also the case for you, try to add a column like this:

EndDate = 
VAR _mrp = [MRP]
VAR _product = [ProductCode]
VAR _table = FILTER(TableName, TableName[ProductCode] = _product)
VAR _maxmy = MAXX( FILTER(_table, TableName[MRP] = _mrp), TableName[MonthYear])
VAR _value = 
MINX(
    FILTER(_table, TableName[MRP]<>_mrp),
    TableName[MonthYear]
)-1
RETURN
IF([Flag]<>"YES", _value)

i tried and it worked like this:

p.s. please consider @ someone, if you still seek further suggestions.