Forum Discussion

cdcarnes's avatar
cdcarnes
Frequent Visitor
1 year ago
Solved

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

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/...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    1 year ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
1 year ago

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's avatar
cdcarnes
Frequent Visitor
1 year ago

That worked.  Thank you so much for your help.