Getting Dynamic Count Of Latest Result With Time Slicer

Question

As you can see in the below dataset there are only 4 unique ID's, however they each have multiple rows of different versions. What I'm attempting to accomplish is create a measure that counts rows but restricted to results with only the latest version of ID, based on the slicer.

Thus these would be correct results based on slicer selection:

• 4/1/2024: 1 open item (55)
• 5/15/2024: 2 open (55,77) and 1 closed (66)
• 8/1/2024: 2 open (88, 55) and 2 closed (77,66)

ID	Version	Modified	Status
88	1	6/11/2024	Open
88	2	6/22/2024	Closed
88	4	7/15/2024	Open
88	6	8/5/2024	Closed
77	1	5/6/2024	Open
77	2	5/25/2024	Open
77	5	7/15/2024	Closed
66	1	4/15/2024	Open
66	3	5/12/2024	Closed
55	1	2/28/2024	Open
55	2	3/28/2024	Open

Other Notes

• I have a Calendar table connected to the Modified dim.
• I also have the Version and ID dims set to whole numbers and do not summarize (which I think is correct?)
• The versions may have gaps, such as 1..3, but they will always be sequentially greater tied with modified (i.e. no ID/version combination will be modified before a lower version with same ID)

What I've Tried

If I use the below Dax function which includes a hardcoded date variable of dMax, I get the exact output of a data table I want based on that dMax variable. But when I've tried to get this into my data visuals, making dMax dynamic (as shown in the commented out line) it includes everything. I know Calculate is supposed to account for context members, but when I do something like Calculate(countRows(daxTable),///several filter efforts\) I keep getting errors or incorrect calcs. Ultimately, I just want to be able to count the rows displayed in this constructed dax table, however, I suspect there's a more efficient way to do this than completely recreate the table? Thank You.

daxTable = 
//VAR dMax = MAX('Calendar'[Date])--cannot tie this to slicer?
VAR dMax = DATE(2024,6,1) -- ← this works as hard coded, but cannot get to slicer

VAR FilteredTable =
    FILTER('sampleTable',
        'sampleTable'[Modified] <= dMax 
    )

var correctManualResult =
       FILTER(FilteredTable,CALCULATE(MAX('sampleTable'[Version]),
                'sampleTable'[Modified] = MAXX(FILTER(FilteredTable, 'sampleTable'[ID] = EARLIER('sampleTable'[ID])), 'sampleTable'[Modified]))>0)
RETURN
    correctManualResult

Answer 1

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.

• Sort data by ID then MODIFIED or Modified (didn't matter
• Added index parameter and checked for items where the previous ID and status was the same as the current row. If true, filtered out.
• Added a OPEN calc column for open items by if open +1 if closed -1
• Created a Closed 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)
• I unpivoted these two columns and then I had a much cleaner data set.
• Once in PBI, I used 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!

End State Of Data

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

M Formula Solution

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"