Dynamic Status Aging

[prasadhebbar315]

Hello everyone,

I am working on a solution to identify Status Aging of cases dynamically. In the below table there are 2 serial numbers. I have the Latest_ML_EPV_Status and ML_Updated_date. I would like to calculate Status_Aging based on the Latest_ML_EPV_Status and when the category occured first.

For Serial Number=HDUMMYFR, Status has been changing but the latest is Moderate and it occured on 03/13/2023. Therefore Status_Aging is 2 days. 

For Serial Number=ABCMMYFR, Status has been changing but the latest is Critical and it occured on 03/11/2023. Therefore Status_Aging is 4 days. 

machine_serial_number	ML_EPV_STATUS	Latest_ML_EPV_Status	ML_Updated_date	Status_Aging
HDUMMYFR	Moderate	Moderate	3/15/2023	2
HDUMMYFR	Moderate	Moderate	3/14/2023	2
HDUMMYFR	Moderate	Moderate	3/13/2023	2
HDUMMYFR	High	Moderate	3/11/2023	2
HDUMMYFR	Moderate	Moderate	3/11/2023	2
HDUMMYFR	Moderate	Moderate	3/9/2023	2
HDUMMYFR	Moderate	Moderate	3/8/2023	2
HDUMMYFR	High	Moderate	3/7/2023	2
HDUMMYFR	High	Moderate	3/6/2023	2
HDUMMYFR	High	Moderate	3/5/2023	2
ABCMMYFR	Critical	Critical	3/15/2023	4
ABCMMYFR	Critical	Critical	3/14/2023	4
ABCMMYFR	Critical	Critical	3/13/2023	4
ABCMMYFR	Critical	Critical	3/11/2023	4
ABCMMYFR	Critical	Critical	3/11/2023	4
ABCMMYFR	Moderate	Critical	3/9/2023	4
ABCMMYFR	Critical	Critical	3/8/2023	4
ABCMMYFR	High	Critical	3/7/2023	4
ABCMMYFR	High	Critical	3/6/2023	4
ABCMMYFR	High	Critical	3/5/2023	4

[Ahmedx · Accepted solution]

1) you can do it as a calculated column

2)You can also do this with measure.

Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26RhkEEF86jKOotUxxR?e=eDMGD4

[CNENFRNL · Accepted solution]

Don't bother to keep [Latest_ML_EPV_Status] in the dataset for this calculation.

[CNENFRNL · Accepted solution]

Don't bother to keep [Latest_ML_EPV_Status] in the dataset for this calculation.

[prasadhebbar315 · Accepted solution]

@CNENFRNL Your solution has worked Thanks

[Ahmedx · Accepted solution]

1) you can do it as a calculated column

2)You can also do this with measure.

Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26RhkEEF86jKOotUxxR?e=eDMGD4

[prasadhebbar315]

@Ahmedx Thanks the solution works