Assigning values in a new column based on dates

[RichOB]

Hi, I need a new column that assigns an entry per property based on dates.

In this scenario, I have 2 properties that need gas checks conducted every year per each property. When we reach the expiry date for a property, a new gas check is performed, and a certification is issued for another year. I need a new column to determine if the checks are being completed on time. The calculation for the new status column is based on the expiry date and the issue date of the next check (per property). Using the table below, I need the column status to be:

- If the issued date of the new check (per property) is the same as the previous expiry date = Completed On Target

- If the issue date is before the previous expiry date = Completed On Target

- If the issue date is later than the previous expiry date = Completed Outside of Target

The first entry per property is blank as they have no previous expiry dates.

Property	Stat_ID	Issue_Date	Expiry_Date	Status (this is the column I need)
1 Lakeshore Drive	G1	01/04/2022	01/04/2023
1 Lakeshore Drive	G2	01/04/2023	01/04/2024	Completed On Target
1 Lakeshore Drive	G3	07/06/2024	07/06/2025	Completed Not On Target
1 Lakeshore Drive	G4	17/06/2025	17/06/2026	Completed Not On Target
22 Giddings St	G5	26/07/2022	26/07/2023
22 Giddings St	G6	29/07/2023	29/07/2024	Completed Not On Target
22 Giddings St	G7	06/08/2024	06/08/2025	Completed Not On Target
22 Giddings St	G8	06/08/2025	06/08/2026	Completed On Target

[v-dineshya]

Hi @RichOB ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @Poojara_D12  and @GrowthNatives , Thank you for your prompt responses.

Hi @RichOB ,

You need the column status to be satisfy the below 3 conditions.

1. If the issued date of the new check (per property) is the same as the previous expiry date = Completed On Target

2. If the issue date is before the previous expiry date = Completed On Target

3. If the issue date is later than the previous expiry date = Completed Outside of Target

Please refer below output snap and attached PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[Poojara_D12]

Hi @RichOB 

What you need is a calculated column that looks at each property’s certification records in sequence, compares the current record’s Issue_Date against the Expiry_Date of the previous record for that same property, and then assigns a status. The logic is straightforward: for the very first record per property, you cannot compare against a prior expiry date, so the status remains blank. For subsequent rows, if the Issue_Date is on or before the previous Expiry_Date, you mark it as Completed On Target, and if the Issue_Date is after the previous Expiry_Date, you mark it as Completed Outside of Target. In DAX, this typically involves using functions like EARLIER/EARLIEST or VAR with CALCULATE(MAX(Expiry_Date)) filtered to rows for the same property with an earlier Issue_Date. By comparing the current row’s Issue_Date to that calculated previous expiry, the column can return the correct status per row. This way, your data will clearly show whether each property’s gas check was completed on time or delayed.

Status =
VAR CurrentProperty = [Property]
VAR CurrentIssue = [Issue_Date]
VAR CurrentID = [Stat_ID]
VAR PrevExpiry =
    CALCULATE (
        MAX ( Table1[Expiry_Date] ),
        FILTER (
            Table1,
            Table1[Property] = CurrentProperty
                && (
                    Table1[Issue_Date] < CurrentIssue
                    || (
                        Table1[Issue_Date] = CurrentIssue
                        && Table1[Stat_ID] < CurrentID
                    )
                )
        )
    )
RETURN
IF (
    ISBLANK ( PrevExpiry ),
    BLANK (),
    IF ( CurrentIssue <= PrevExpiry, "Completed On Target", "Completed Outside of Target" )
)

[GrowthNatives]

Hi @RichOB,

We’ll add a calculated column that, per property, looks up the most recent previous Expiry_Date and compares it to the current Issue_Date.

Calculated column:

Status =
VAR CurrProp = 'Checks'[Property]
VAR CurrIssue = 'Checks'[Issue_Date]
VAR PrevExpiry =
CALCULATE(
MAX('Checks'[Expiry_Date]),
FILTER(
ALL('Checks'),
'Checks'[Property] = CurrProp
&& 'Checks'[Issue_Date] < CurrIssue
)
)
RETURN
IF(
ISBLANK(PrevExpiry),
BLANK(),
IF(CurrIssue <= PrevExpiry, "Completed On Target", "Completed Outside of Target")
)

Make sure Issue_Date and Expiry_Date are typed as Date.

This DAX logic leaves the first row for each property blank (since there’s no previous certificate), then marks entries as “Completed On Target” when the new Issue_Date is on or before the previous Expiry_Date, and as “Completed Outside of Target” when the new Issue_Date is after the previous Expiry_Date.

⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together! [Explore More]🚀