Forum Discussion

RichOB's avatar
RichOB
Icon for Post Partisan rankPost Partisan
11 months ago
Solved

Assigning values in a new column based on dates

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

- If the expiry date of the most recent Gas_ID is before the date today and there is no new Gas_ID, check for that property = Not Completed 

- The expiry date of the most recent check is in the future = Current 

 

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

 

PropertyStat_IDIssue_DateExpiry_DateStatus (this is the column I need)
1 Lakeshore DriveG101/04/202201/04/2023 
1 Lakeshore DriveG201/04/202301/04/2024Completed On Target
1 Lakeshore DriveG307/06/202407/06/2025Completed Not On Target
1 Lakeshore DriveG407/06/202507/06/2026Current
22 Giddings StG526/07/202226/07/2023 
22 Giddings StG629/07/202329/07/2024Completed Not On Target
22 Giddings StG706/08/202406/08/2025Completed Not On Target
22 Giddings StG801/09/202501/09/2026Current

 

Thanks in advance!

Need Help
  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    11 months ago

    Thankyou, mh2587, for your response.

    Hi RichOB,

    We appreciate your enquiry submitted via the Microsoft Fabric Community Forum.

    Based on my understanding of the situation, please find attached a screenshot and a sample .pbix file that may help resolve the issue:


    We hope the information provided is useful. Should you have any further queries, please feel free to contact the Microsoft Fabric Community.

    Thank you.

6 Replies

  • mh2587's avatar
    mh2587
    Icon for Super User rankSuper User
    11 months ago
    Status = // Try this might help you and change the "Test" with your table name
VAR CurrentProperty   = Test[Property]
VAR CurrentIssueDate  = Test[Issue_Date]
VAR CurrentExpiryDate = Test[Expiry_Date]
VAR CurrentStatID     = Test[Stat_ID]

-- get the previous expiry date for the same property
VAR PrevExpiryDate =
    MAXX (
        FILTER (
            Test,
            Test[Property] = CurrentProperty &&
            Test[Issue_Date] < CurrentIssueDate
        ),
        Test[Expiry_Date]
    )

-- today’s date
VAR Today = TODAY()

RETURN
IF (
    ISBLANK ( PrevExpiryDate ),
    BLANK(),  -- first record per property is blank
    SWITCH (
        TRUE(),
        -- If the expiry of the most recent record is in the future
        CurrentExpiryDate > Today
            && CurrentStatID = MAXX ( FILTER ( Test, Test[Property] = CurrentProperty ), Test[Stat_ID] ),
            "Current",

        -- If expiry is before today and it’s the last record for property
        CurrentExpiryDate < Today
            && CurrentStatID = MAXX ( FILTER ( Test, Test[Property] = CurrentProperty ), Test[Stat_ID] ),
            "Not Completed",

        -- Issued = prev expiry
        CurrentIssueDate = PrevExpiryDate, "Completed On Target",

        -- Issued before prev expiry
        CurrentIssueDate < PrevExpiryDate, "Completed On Target",

        -- Issued after prev expiry
        CurrentIssueDate > PrevExpiryDate, "Completed Not On Target"
    )
)
  • RichOB's avatar
    RichOB
    Icon for Post Partisan rankPost Partisan
    11 months ago

    Hi mh2587 thank so much for such a detailed reply. 

     

    The more I think of this, it needs to be split into 2 measures. 

     

    1st measure  - for the Completed On Target, Completed Outside Target, Not Completed

    2nd measure -for Current, Not Current

     

    If a property has a status of Current, it won't show if it was Completed In Target or Outside of target, so we'd be missing that data for the most recent check for every property. I'll accept this as a solution and re-post another one with that info. Thanks again.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Icon for Community Support rankCommunity Support
    11 months ago

    Thankyou, mh2587, for your response.

    Hi RichOB,

    We appreciate your enquiry submitted via the Microsoft Fabric Community Forum.

    Based on my understanding of the situation, please find attached a screenshot and a sample .pbix file that may help resolve the issue:


    We hope the information provided is useful. Should you have any further queries, please feel free to contact the Microsoft Fabric Community.

    Thank you.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Icon for Community Support rankCommunity Support
    11 months ago

    Hi RichOB,

    We would like to follow up and see whether the details we shared have resolved your problem. If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

    Thank you.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Icon for Community Support rankCommunity Support
    11 months ago

    Hi RichOB,

    We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

    Thank you.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Icon for Community Support rankCommunity Support
    11 months ago

    Hi RichOB,

    We wanted to see if the information we gave helped fix your problem. If you need more help, please feel free to contact the Microsoft Fabric community.

    Thank you.