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 exp...
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.

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"
    )
)