Forum Discussion
Help Building Deadlines for Single Items with Multiple IDs
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!
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!
11 Replies
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) )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") )
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/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.
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"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.
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 ]} } )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 LearnThank you.
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!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!
