Help Building Deadlines for Single Items with Multiple IDs

[quincy_p]

Hi everyone - long question here - I am trying to build KPIs for my team but running into some issues with the way our data is managed. 

Long story short - I need to measure our performance servicing contracts, each contract has a service frequency (6 or 12 months) and has to be serviced for the duration of the contract.

The issue is that we can have mutliple versions of the same contract in our Salesforce instance - so the same Name but a different unique ID - this makes tracking the services against them difficult becasue the service work order is related to the contract by Id, not name.

'Service Contract'[Name]  = 'Work Order All' [Service_Contract_Name]

Additionally, contracts are entered in different ways but need to be tracked a single item, see below for examples. What I essentially need to do is breakdown each contract into its service deadlines (Start Date + Frequency = Deadline 1, Deadline 1 + Frequency = Deadline 2 and so on).

Row #	Contract Name	Id	Start Date	End Date	Frequency
1	NL-123456	a123456789	01/01/2024	31/12/2028	12
2	NL-987654	a987654321	01/01/2023	31/12/2026	6
3	NL-987654	a567891234	01/01/2023	31/12/2026	6
4	NL-567567	b123456789	01/01/2024	31/12/2024	12
5	NL-567567	b987654321	01/01/2025	31/12/2025	12

Row #1 is the ideal scenario, I can find each associated work order to see if it was serviced within each year.

Rows #2-3 - a contract can be terminated and recreated but the Terminated version could have 4 work orders associated, and the new contract then would have 0 (even though they are the same contract).

Rows #4-5 - same as above.

I tried to take the following approach to see a single contract - but essentially, for each Contract Name I have pulled the MIN Start Date and Max End Date to get the entire lifecycle, then I pulled the associated Id of the Max End Date as this has the best chance of being active in the system.

let
    Source = PowerPlatform.Dataflows(null),
    Workspaces = Source{[Id="Workspaces"]}[Data],
    #"169eb939-15ea-4fd2-818c-13d66178b545" = Workspaces{[workspaceId="169eb939-15ea-4fd2-818c-13d66178b545"]}[Data],
    #"75b17dd5-cafc-488c-a448-48508445c89f" = #"169eb939-15ea-4fd2-818c-13d66178b545"{[dataflowId="75b17dd5-cafc-488c-a448-48508445c89f"]}[Data],
    #"Service/Maintenance Contract_" = #"75b17dd5-cafc-488c-a448-48508445c89f"{[entity="Service/Maintenance Contract",version=""]}[Data],

    // Rename Name -> Contract Number
    #"Renamed Columns" = Table.RenameColumns(#"Service/Maintenance Contract_",{{"Name", "Contract Number"}}),

    // Group by Contract Number
    #"Grouped Rows" = Table.Group(
        #"Renamed Columns",
        {"Contract Number"},
        {
            {"Original_Start_Date", each List.Min([SVMXC__Start_Date__c]), type nullable date},
            {"Final_End_Date", each List.Max([SVMXC__End_Date__c]), type nullable date},
            {"AllRows", each _, type table [
                Id=nullable text, 
                OwnerId=nullable text, 
                Contract Number=nullable text, 
                CreatedDate=nullable datetime, 
                SVMXC__Start_Date__c=nullable date, 
                SVMXC__End_Date__c=nullable date,
                SVMXC__Company__c=nullable text, 
                SVMXC__EndpointURL__c=nullable text, 
                Contract_Status__c=nullable text, 
                External_ID__c=nullable text, 
                SVMX_Expiration_Status__c=nullable text,
                Country_OU__c=nullable text, 
                ERP_Account_Number__c=nullable text,
                EU_SLA_Terms__c=nullable text, 
                Medical_Medsurg__c=nullable text, 
                Qualified_Covered_Product_Count__c=nullable number, 
                Total_Active_Covered_Products__c=nullable number
            ]}
        }
    ),

    // Select the row inside AllRows with the MAX Final_End_Date
    #"MaxRow Added" = Table.AddColumn(
        #"Grouped Rows",
        "RowWithMaxEnd",
        each Table.SelectRows(
            [AllRows],
            (r) => r[SVMXC__End_Date__c] = [Final_End_Date]
        ),
        type table
    ),

    // Expand Id AND Contract_Status__c from the max-end row
    #"Expanded MaxRow" = Table.ExpandTableColumn(
        #"MaxRow Added",
        "RowWithMaxEnd",
        {"Id", "Contract_Status__c"},
        {"Latest_Id", "Latest_Contract_Status"}
    )
in
    #"Expanded MaxRow"

Then from there I could build deadlines:

PM1 Deadline = 
    EDATE('Service/Maintenance Contract (2025 Update)'[Original_Start_Date], 'Service/Maintenance Contract (2025 Update)'[Contract_PM_Frequency]) - 1

PM2 Deadline = 
                EDATE('Service/Maintenance Contract (2025 Update)'[PM1 Deadline], 'Service/Maintenance Contract (2025 Update)'[Contract_PM_Frequency])

But now I am stuck. Essentially I need to now have some sort of connection that where 'Service Contract'[Name]  = 'Work Order All' [Service_Contract_Name], is there a 'Work Order All' [ Actual_Resolution_Date] before each Deadline. And if it is not before each deadline, was teh service missed entirely or late?

For example:
Deadline1 = 31/12/23

Service 1 = 15/12/23

Result = On Time 

Deadline2 = 31/12/24

Service 2 = 05/01/25

How can I distinguis this from being recognized as service 3 and being a late service 2?

I built a Union Table to pull all contracts and deadlines:

PM_Deadlines_Union = 
UNION (
    SELECTCOLUMNS (
        FILTER (
            'Service/Maintenance Contract (2025 Update)',
            'Service/Maintenance Contract (2025 Update)'[Final_End_Date] >= TODAY () - 90
        ),
        "ContractID", [Latest_Id],
        "ContractName", [Contract Number],
        "Deadline", [PM1 Deadline],
        "PM_Number", "PM1",
        "ContractStatus", [Latest_Contract_Status]
    ),
    SELECTCOLUMNS (
        FILTER (
            'Service/Maintenance Contract (2025 Update)',
            'Service/Maintenance Contract (2025 Update)'[Final_End_Date] >= TODAY () - 90
        ),
        "ContractID", [Latest_Id],
        "ContractName", [Contract Number],
        "Deadline", [PM2 Deadline],
        "PM_Number", "PM2",
        "ContractStatus", [Latest_Contract_Status]
    ),
    SELECTCOLUMNS (
        FILTER (
            'Service/Maintenance Contract (2025 Update)',
            'Service/Maintenance Contract (2025 Update)'[Final_End_Date] >= TODAY () - 90
        ),
        "ContractID", [Latest_Id],
        "ContractName", [Contract Number],
        "Deadline", [PM3 Deadline],
        "PM_Number", "PM3",
        "ContractStatus", [Latest_Contract_Status]
    ),
    SELECTCOLUMNS (
        FILTER (
            'Service/Maintenance Contract (2025 Update)',
            'Service/Maintenance Contract (2025 Update)'[Final_End_Date] >= TODAY () - 90
        ),
        "ContractID", [Latest_Id],
        "ContractName", [Contract Number],
        "Deadline", [PM4 Deadline],
        "PM_Number", "PM4",
        "ContractStatus", [Latest_Contract_Status]
    ),
    SELECTCOLUMNS (
        FILTER (
            'Service/Maintenance Contract (2025 Update)',
            'Service/Maintenance Contract (2025 Update)'[Final_End_Date] >= TODAY () - 90
        ),
        "ContractID", [Latest_Id],
        "ContractName", [Contract Number],
        "Deadline", [PM5 Deadline],
        "PM_Number", "PM5",
        "ContractStatus", [Latest_Contract_Status]
    )
)

And did the following check:

Resolved_Between_Previous_PM = 
VAR ContractID = 'PM_Deadlines_Union'[ContractID]
VAR PMDeadline = 'PM_Deadlines_Union'[Deadline]
VAR PrevDeadline =
    CALCULATE(
        MAX('PM_Deadlines_Union'[Deadline]),
        FILTER(
            'PM_Deadlines_Union',
            'PM_Deadlines_Union'[ContractID] = ContractID &&
            'PM_Deadlines_Union'[Deadline] < PMDeadline
        )
    )
VAR MatchingRows =
    CALCULATETABLE(
        FILTER(
            'Summary_PM_Parent_WO',
            NOT(ISBLANK('Summary_PM_Parent_WO'[SVMXC__Actual_Resolution__c])) &&
            'Summary_PM_Parent_WO'[SVMXC__Actual_Resolution__c] > PrevDeadline &&
            'Summary_PM_Parent_WO'[SVMXC__Actual_Resolution__c] <= PMDeadline
        ),
        TREATAS({ ContractID }, 'Summary_PM_Parent_WO'[SVMXC__Service_Contract__c])
    )
RETURN
IF(COUNTROWS(MatchingRows) > 0, "Between PMs", "No Match")

But I now face the issue of not knowing if the service is on time, or incorrectly hadded to the wrong deadline. Additionally, when I filter on a specific deadline year - it pulls all rows and adds the same deadlines, rather than linking each specific one to the date:

Where have a I gone wrong? And guidance would be greatly appreciated!

[v-sgandrathi · Accepted solution]

Hi @quincy_p,

we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.
Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[quincy_p]

Hi @v-sgandrathi I'm marking as accepted as this achieved a pretty close solution for what I need. I still think I can have an alternative approach with different data so I am going to raise a new question to see how the results compare.

Really appreciate all the work you have done here!

[v-sgandrathi]

Hi @quincy_p,

Thank you @Jaywant-Thorat and @amitchandak for your response to the query.

The reason your Cycles column is returning null is because the Contract_PM_Frequency field is missing from the grouped table after the Group By step. In Power Query, columns not aggregated during grouping are removed, so the frequency value only remains inside the AllRows sub-table. As a result, the Cycles step cannot access it and returns null for each row. To resolve this, extract the frequency during the Group By step by adding an aggregated column, such as PM_Frequency = each List.Max([Contract_PM_Frequency]), in Table.Group. This will make the PM_Frequency field available at the grouped level, allowing your Cycles calculation to reference it. With Start, End, and Frequency in the same row, the List.Generate function can create the correct list of PM deadlines, and expanding the Cycles list will yield one row per deadline as intended.

Thank you.

[quincy_p]

Sorry for the delay - I am not sure what you mean (do not know power query well enough) - below is my group by.

Are you suggesting to add into this query or to add it as an additonal step before/after?

= Table.Group(
        #"Renamed Columns",
        {"Contract Number"},
        {
            {"Original_Start_Date", each List.Min([SVMXC__Start_Date__c]), type nullable date},
            {"Final_End_Date", each List.Max([SVMXC__End_Date__c]), type nullable date},
            {"AllRows", each _, type table [
                Id=nullable text, 
                OwnerId=nullable text, 
                Contract Number=nullable text, 
                CreatedDate=nullable datetime, 
                SVMXC__Start_Date__c=nullable date, 
                SVMXC__End_Date__c=nullable date,
                SVMXC__Company__c=nullable text, 
                SVMXC__EndpointURL__c=nullable text, 
                Contract_Status__c=nullable text, 
                External_ID__c=nullable text, 
                SVMX_Expiration_Status__c=nullable text,
                Country_OU__c=nullable text, 
                ERP_Account_Number__c=nullable text,
                EU_SLA_Terms__c=nullable text, 
                Medical_Medsurg__c=nullable text, 
                Qualified_Covered_Product_Count__c=nullable number, 
                Total_Active_Covered_Products__c=nullable number
            ]}
        }
    )

[v-sgandrathi]

Hi @quincy_p,

In Power Query, using Group By will remove any columns that are not aggregated from the grouped output. As a result, your Contract_PM_Frequency value was only present within the AllRows table, making it inaccessible to the Add Cycles step. To resolve this, you should include the frequency field in the Table.Group aggregation, such as:

{"PM_Frequency", each List.Max([Contract_PM_Frequency]), type number}

After adding this field, the grouped row now includes all necessary values (Start Date, End Date, and Frequency), which enables the Cycles list to generate properly with List.Generate(). Here is the corrected structure: group by Contract Number and include Start Date, End Date, and PM_Frequency; use the new [PM_Frequency] column in the Add Cycles step; and expand the Cycles list to create one row per deadline.

This update will ensure that deadlines are calculated correctly and will prevent any null values.

Table.Group - PowerQuery M | Microsoft Learn
List.Generate - PowerQuery M | Microsoft Learn
Type functions - PowerQuery M | Microsoft Learn

Thank you.

[v-sgandrathi]

Hi @quincy_p,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

Thank you.

[quincy_p]

I added that the my Contracts Table but all of the Cycles values return as Null?

let
    Source = PowerPlatform.Dataflows(null),
    Workspaces = Source{[Id="Workspaces"]}[Data],
    #"169eb939-15ea-4fd2-818c-13d66178b545" = Workspaces{[workspaceId="169eb939-15ea-4fd2-818c-13d66178b545"]}[Data],
    #"75b17dd5-cafc-488c-a448-48508445c89f" = #"169eb939-15ea-4fd2-818c-13d66178b545"{[dataflowId="75b17dd5-cafc-488c-a448-48508445c89f"]}[Data],
    #"Service/Maintenance Contract_" = #"75b17dd5-cafc-488c-a448-48508445c89f"{[entity="Service/Maintenance Contract",version=""]}[Data],

    // Rename Name -> Contract Number
    #"Renamed Columns" =
        Table.RenameColumns(
            #"Service/Maintenance Contract_",
            {{"Name", "Contract Number"}}
        ),

    // Group by Contract Number
    #"Grouped Rows" =
        Table.Group(
            #"Renamed Columns",
            {"Contract Number"},
            {
                {"Original_Start_Date", each List.Min([SVMXC__Start_Date__c]), type nullable date},
                {"Final_End_Date", each List.Max([SVMXC__End_Date__c]), type nullable date},
                {"AllRows", each _, type table [
                    Id=nullable text, 
                    OwnerId=nullable text, 
                    Contract Number=nullable text, 
                    CreatedDate=nullable datetime, 
                    SVMXC__Start_Date__c=nullable date, 
                    SVMXC__End_Date__c=nullable date,
                    SVMXC__Company__c=nullable text, 
                    SVMXC__EndpointURL__c=nullable text, 
                    Contract_Status__c=nullable text, 
                    External_ID__c=nullable text, 
                    SVMX_Expiration_Status__c=nullable text,
                    Country_OU__c=nullable text, 
                    ERP_Account_Number__c=nullable text,
                    EU_SLA_Terms__c=nullable text, 
                    Medical_Medsurg__c=nullable text, 
                    Qualified_Covered_Product_Count__c=nullable number, 
                    Total_Active_Covered_Products__c=nullable number
                ]}
            }
        ),

    // Select rows where SVMXC__End_Date__c = Final_End_Date
    #"MaxRow Added" =
        Table.AddColumn(
            #"Grouped Rows",
            "RowWithMaxEnd",
            each Table.SelectRows(
                [AllRows],
                (r) => r[SVMXC__End_Date__c] = [Final_End_Date]
            ),
            type table
        ),

    // Expand Id and Contract_Status__c from max-end row
    #"Expanded MaxRow" =
        Table.ExpandTableColumn(
            #"MaxRow Added",
            "RowWithMaxEnd",
            {"Id", "Contract_Status__c"},
            {"Latest_Id", "Latest_Contract_Status"}
        ),

    // Add Cycle list column (list of deadlines)
    #"Add Cycles" =
        Table.AddColumn(
            #"Expanded MaxRow",
            "Cycles",
            each 
                let
                    Start = [Original_Start_Date],
                    End = [Final_End_Date],
                    // You may need to replace Contract_PM_Frequency with the real field name
                    Freq = try Number.From([Contract_PM_Frequency]) otherwise null,
                    Deadlines =
                        if Start <> null and End <> null and Freq <> null then
                            List.Generate(
                                () => Start,
                                each _ <= End,
                                each Date.AddMonths(_, Freq)
                            )
                        else
                            null
                in Deadlines
        ),

    // Expand Cycles into rows
    #"Expand Cycles" =
        Table.ExpandListColumn(#"Add Cycles", "Cycles")

in
    #"Expand Cycles"

[Jaywant-Thorat]

Understanding your Problem: You want, for each contract, for each deadline, was a service completed on time, late, or missed, even when Salesforce stores multiple contract IDs for the same actual contract.
Did I frame your problem scenario right?

-------------------------------------------

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Jaywant Thorat | MCT | Data Analytics Coach
Linkedin: https://www.linkedin.com/in/jaywantthorat/

[quincy_p]

Yes that is basically it. Each contract has a frequency of service and a contract length so if all were entered in the same, correct way this would be fine, but I need to base it not off the unqiue ID from Salesforce but rather my approach is to combine all unique IDs that share the same contract number and work from there. 

[amitchandak]

@quincy_p , Try these in power query - Contract Table

// Add Cycle list column (list of deadlines)
#"Add Cycles" =
    Table.AddColumn(
        #"Expanded MaxRow",
        "Cycles",
        each 
            let
                Start = [Original_Start_Date],
                End = [Final_End_Date],
                Freq = [Contract_PM_Frequency],
                Deadlines =
                    List.Generate(
                        ()=> Start,
                        each _ <= End,
                        each Date.AddMonths(_, Freq)
                    )
            in Deadlines
    ),

// Expand into rows
#"Expand Cycles" =
    Table.ExpandListColumn(#"Add Cycles", "Cycles")

new cycle no in work order

CycleNumber =
Number.RoundUp(
    Duration.Days([Actual_Resolution_Date] - ContractStartDate) 
        / (FreqMonths * 30.4375)
)

[amitchandak]

@quincy_p ,  and these measures , if they can help

haswo =
if(countrows(relatedtable(workorders)) > 0, 1, 0)

OnTime =
var wodate = calculate( min(workorders[actual_resolution_date]) )
var deadline = selectedvalue(contractdeadlines[deadline])
return
if(isblank(wodate), "missed", if(wodate <= deadline, "on time", "late") )