Dynamic Aging Calculation

[prasadhebbar315]

Hello everyone,

I am trying to calculate aging of status dynamically based on current date and first date of occurence.

In the below example, the first 3 cols are available in the source data and I need help in calculating "Latest_Status" and "Status_Aging".

Latest_Status: By Serial_Number, Status based on max(Updated_Date). For 100, Latest_Status=Critical since max(Updated_Date)=3/2/2023 and the Status is Critical.

Status_Aging: I need to calculate aging of each categories in Status column by Serial_Number based on when it occured first. For Serial_Number 100, Critical first appeared on 2/28/2023 hence aging =0 for that day, 1 on 3/1/2023 and 2 on 3/2/2023.

Both these calculations must be dynamic based on dates and status.

Serial_Number	Updated_Date	Status	Latest_Status	Status_Aging
100	3/2/2023	Critical	Critical	2
100	3/1/2023	Critical	Critical	1
100	2/28/2023	Critical	Critical	0
100	2/27/2023	High	Critical	0
100	2/26/2023	Moderate	Critical	0
100	2/25/2023	Low	Critical	1
100	2/24/2023	Low	Critical	0
200	2/20/2023	Moderate	Moderate	0
300	2/10/2023	High	High	3
300	2/9/2023	High	High	2
300	2/8/2023	High	High	1
300	2/7/2023	High	High	0
300	2/6/2023	Low	High	4
300	2/5/2023	Low	High	3
300	2/4/2023	Low	High	2
300	2/3/2023	Low	High	1
300	2/2/2023	Low	High	0

[prasadhebbar315]

@Ahmedx Thanks for the solution. This solution works now but if the updated date is not present for 02.03.2023 and it is available on 03.03.2023, your calculation is skipping the day and assinging values based on rankx. In the below example: ML_Updated_Date is missing for 02.03.2023. Ideally status aging should be 2 for 02.03.2034 and 3 for 03.03.2023.

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26Rhg7-ZkuSfG9VrW3a?e=CMtTvs