Count items on the most recent document by ID withing selected date range

[cdcarnes]

I am having a hard time figuring this one out.   I have a table similar to the one shown below

EffectiveDate	ID	NoProblems
4/1/2025	1	Y
3/31/2025	5	NULL
3/28/2025	4	Y
3/26/2025	6	Y
3/21/2025	10	Y
3/21/2025	11	Y
3/18/2025	1	NULL
3/18/2025	5	Y
3/18/2025	12	Y
3/18/2025	11	Y
3/18/2025	8	Y
3/18/2025	6	NULL
3/18/2025	7	Y
3/18/2025	5	Y
3/18/2025	1	NULL
3/7/2025	15	Y
3/7/2025	20	Y
3/7/2025	21	Y
3/7/2025	24	Y
3/7/2025	3	Y

I need to be able to provide a count of the "Y" in the table based on the most recent document that is within the date range selected on the slicer.  

So, if someon selected a date range of 3-7 to 3-20 it would return a value of 8 (ID 1 has a Y value but most recent document is NULL so would not count them and ID 5 is listed twice with most recent document a "Y" value so only count them one time).

I can't seem to figure out how to get this completed.  My first thought was to create a virtual table based on the date ranges. But I can't keep it from duplicating data and only selecting the most recent document in that date range. 

Any help would be greatly appreciated.

[Greg_Deckler · Accepted solution]

@cdcarnes Try:

Measure = 
  VAR __Table = 
    ADDCOLUMNS(
        SUMMARIZE( 'Table', [ID], "MaxDate", MAX('Table'[EffectiveDate] ) ),
        "__NoProblems", MAXX(FILTER( 'Table', [ID] = EARLIER('Table'[ID]) && [EffectiveDate] = [MaxDate]), [NoProblems] )
    )
  VAR __Table2 = FILTER( __Table, [__NoProblems] = "Y" )
  VAR __Result = COUNTROWS( __Table2 )
RETURN
  __Result

Pretty sure the answer is 10 not 8. PBIX attached below signature.

[cdcarnes]

That worked.  Thank you so much for your help.

[pankajnamekar25]

Hello @cdcarnes 

try below DAX

CountOfYOnMostRecent =

VAR DateRangeTable =

    FILTER(

        YourTable,

        YourTable[EffectiveDate] >= MIN('DateTable'[Date]) &&

        YourTable[EffectiveDate] <= MAX('DateTable'[Date])

    )

VAR MostRecentPerID =

    ADDCOLUMNS(

        SUMMARIZE(DateRangeTable, YourTable[ID]),

        "MostRecentDate",

            CALCULATE(

                MAX(YourTable[EffectiveDate]),

                DateRangeTable,

                ALLEXCEPT(YourTable, YourTable[ID])

            )

    )

VAR MostRecentRecords =

    FILTER(

        ADDCOLUMNS(

            MostRecentPerID,

            "NoProblemsValue",

                CALCULATE(

                    MAX(YourTable[NoProblems]),

                    FILTER(

                        DateRangeTable,

                        YourTable[ID] = EARLIER(YourTable[ID]) &&

                        YourTable[EffectiveDate] = EARLIER([MostRecentDate])

                    )

                )

        ),

        [NoProblemsValue] = "Y"

    )

RETURN

    COUNTROWS(MostRecentRecords)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[cdcarnes]

Doesn't seem to be working.  Is returning 0 on my table (not the sample above) when it should be 3.