Forum Discussion
undefined
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.
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.
4 Replies
hi Anonymous
do you have other date column in other format beside this one - "jan, 2022"?
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.
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.
