Create column with the closest date of the same table considering the IDs

[Syndicate_Admin]

Good afternoon

I'm new to power BI and would like your help with a DAX that does the following:

1. I have a table with several computers with the start and end dates of their maintenance.
2. I want to create a column that places the previous maintenance end date (column: PreviusDate) for each computer and for each row.
3. When the equipment has no previous maintenance, it should be left blank.

Attached data in Excel and Power BI file.

https://1drv.ms/u/s!Ah2kspv1jfcTgbJSmMPQNpqKuAdd5g?e=7tzgCM

Thank you in advance for your help

Best regards

Jesus

[Syndicate_Admin]

Thanks a lot! It's what I needed.

[v-xiaosun-msft · Accepted solution]

Hi @Syndicate_Admin ,

I wonder whether I have understood your requirement correctly. I think that you need "PreviusDate" for each computer. Here is my suggestion.

You can create a column as below.

column =
CALCULATE (
    MAX ( 'Data'[FIN] ),
    FILTER (
        'Data',
        'Data'[COD NISIRA] = EARLIER ( Data[COD NISIRA] )
            && 'Data'[FIN] < EARLIER ( Data[FIN] )
    )
)

Final output:

Best Regards,
Community Support Team _ xiaosun

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