As was mentioned in the comments, I was approaching this incorrectly and ultimately reached a solution before the data arrived in PBI by leveraging M-Language/Power Query vs. Dax.
A key oversite by me was that I was only calculating when the status toggled between OPEN and CLOSED. Thus I could simplify the process by filtering out repetitive items such as ID = look to filter out meaningless data such as duplicate ID's where the status didn't change (such as ID 77 version 1 → 2).
The source code for what I used to solve the above sample data is below in M-Language format, but I'll summarize what I did.
OPEN calc column for open items by if open +1 if closed -1Closed calc column, but this was more tricky as I needed another lookup to previous record to see if status of same ID had changed from CLOSED to OPEN which in this case would reduce the closed item count (see ID 88)AllItems = CALCULATE(SUM('StatusTable'[Value])) and it showed the Items as expected.Hope this helps others.
Personal Note Today is Thanksgiving in America and I do like the idea of Gratitude as a concept (like for real... I've been to India some people/kids really have it tough 😕!). However somewhere in America, this holiday took a wrong turn when it became okay for in-laws to invite themselves over, irrespective of what the homeowner (me) says... Is it bad that I feel way more comfortable rambling on about the above technical challenge contrasted to walking out of my home office to the family warzone where I'm bound to encounter annoying inlaws and simpleton siblings... ??? Oh good, I thought I was the only one! Happy Thanksgiving StackOverflow!
This is what was fed to Power BI
ID Version Modified Status Value
55 1 2/28/2024 Open 1
66 1 4/15/2024 Open 1
66 3 5/12/2024 Open -1
66 3 5/12/2024 Closed 1
77 1 5/6/2024 Open 1
77 5 7/15/2024 Open -1
77 5 7/15/2024 Closed 1
88 1 6/11/2024 Open 1
88 2 6/22/2024 Open -1
88 2 6/22/2024 Closed 1
88 4 7/15/2024 Open 1
88 4 7/15/2024 Closed -1
88 6 8/5/2024 Open -1
88 6 8/5/2024 Closed 1
This could probably be cleaned up, but my inlaws are asking my kids to play UNO which I can't let them endure without support so gotta go.
let
cClose = "Closed",
ooOpen = "Open",
startTable=
Table.FromRecords({
[ID = 88, Version = 1, Modified = #date(2024,06,11), Status = ooOpen],
[ID = 88, Version = 2, Modified = #date(2024,06,22), Status = cClose],
[ID = 88, Version = 4, Modified = #date(2024,07,15), Status = ooOpen ],
[ID = 88, Version = 6, Modified = #date(2024,08,05), Status = cClose ],
[ID = 77, Version = 1, Modified = #date(2024,05,06), Status = ooOpen],
[ID = 77, Version = 2, Modified = #date(2024,05,25), Status = ooOpen],
[ID = 77, Version = 5, Modified = #date(2024,07,15), Status = cClose],
[ID = 66, Version = 1, Modified = #date(2024,04,15), Status = ooOpen],
[ID = 66, Version = 3, Modified = #date(2024,05,12), Status = cClose],
[ID = 55, Version = 1, Modified = #date(2024,02,28), Status = ooOpen],
[ID = 55, Version = 2, Modified = #date(2024,03,28), Status = ooOpen]
}),
setTypes = Table.TransformColumnTypes(startTable,{{"Version", Int64.Type},
{"ID", Int64.Type},{"Modified", type date}, {"Status", type text}}),
#"Sorted Rows" = Table.Sort(setTypes,{{"ID", Order.Ascending},{"Modified",Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
fixColumnOrder = Table.ReorderColumns(#"Sorted Rows" ,{"ID", "Status", "Version", "Modified"}),
checkColMatches = let
zTable = fixColumnOrder,
zIDList = Table.Column(zTable,"ID"),
zStatusList = Table.Column(zTable,"Status"),
addIndex = Table.AddIndexColumn(zTable, "Index", 0, 1, Int64.Type),
testResult = Table.AddColumn(addIndex, "CheckForMatches", each
if [Index] = 0 then false else
if zIDList{[Index]-1} = [ID] then zStatusList{[Index]-1} = [Status] else false, type logical),
endResult = Table.RemoveColumns(testResult,{"Index"})
in
endResult,
#"Filtered Rows" = Table.SelectRows(checkColMatches, each ([CheckForMatches] = false)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"CheckForMatches"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", ooOpen, each if [Status] = ooOpen then 1 else -1, Int64.Type),
createClosedIncludingDoOvers =
let
zTable = #"Added Custom",
idListAgain = Table.Column(zTable,"ID"),
plusIndex = Table.AddIndexColumn(zTable,"Index",0,1,Int64.Type),
AddedClosedItems = Table.AddColumn(plusIndex, cClose, each if [Status] = cClose then 1 else if [Index] = 0 then 0 else if (idListAgain{[Index]-1} = [ID]) and ([Status] = "Open") then -1 else 0 ,Int64.Type)
in
Table.RemoveColumns(AddedClosedItems,{"Index"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(createClosedIncludingDoOvers, {"ID", "Status", "Version", "Modified"}, "Items", "Value"),
RemovedZeros = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> 0)),
#"Removed Columns1" = Table.RemoveColumns(RemovedZeros,{"Status"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Items", "Status"}})
in
#"Renamed Columns"