Goal: Count cases outstanding on the WC date selected from your DateTable, given:

• Active rel.: DateTable[Date] → FactTable[ReceivedDate]
• Inactive rel.: DateTable[Date] → FactTable[ClearedDate]

  Definition (as-of logic): A row is outstanding on date D if:

  1. ReceivedDate < D (received before the WC date), and
  2. ClearedDate is blank or ClearedDate ≥ D.

     1) Core measure (as-of count)

     This measure captures the selected WC date from the date slicer, then ignores the active date relationship when evaluating the fact rows (so you don’t accidentally pre-filter by ReceivedDate). It still respects other report/page slicers (e.g., BU, Region).

     // Outstanding cases as of the selected WC date (received strictly before D)
     Outstanding As Of =
     VAR D =
         MAX ( 'DateTable'[WC] )               // single WC selection or iterates per axis context
     RETURN
     CALCULATE (
         COUNTROWS ( 'FactTable' ),
         FILTER (
             ALLSELECTED ( 'FactTable' ),      // keep non-date slicers
             'FactTable'[ReceivedDate] < D
                 && ( ISBLANK ( 'FactTable'[ClearedDate] )
                      || 'FactTable'[ClearedDate] >= D )
         ),
         REMOVEFILTERS ( 'DateTable' )         // break active ReceivedDate relationship
     );

     Why this pattern?

     • REMOVEFILTERS('DateTable') breaks the active ReceivedDate filter. We use the date slicer only to capture D.
     • ALLSELECTED(FactTable) preserves user slicers on other dimensions while preventing unintended date pre-filtering.
     • Direct date comparisons avoid toggling the inactive ClearedDate relationship.

       2) Optional diagnostics (to reconcile numbers)

       Received before D

       Received Before D :=
       VAR D = MAX ( 'DateTable'[WC] )
       RETURN
       CALCULATE (
           COUNTROWS ( 'FactTable' ),
           FILTER ( ALLSELECTED ( 'FactTable' ), 'FactTable'[ReceivedDate] < D ),
           REMOVEFILTERS ( 'DateTable' )
       )

       Currently outstanding (Cleared is blank)

       Currently Outstanding Before D :=
       VAR D = MAX ( 'DateTable'[WC] )
       RETURN
       CALCULATE (
           COUNTROWS ( 'FactTable' ),
           FILTER ( ALLSELECTED ( 'FactTable' ),
               'FactTable'[ReceivedDate] < D
               && ISBLANK ( 'FactTable'[ClearedDate] )
           ),
           REMOVEFILTERS ( 'DateTable' )
       )

       Cleared on/after D

       Cleared OnOrAfter D (from those received before D) :=
       VAR D = MAX ( 'DateTable'[WC] )
       RETURN
       CALCULATE (
           COUNTROWS ( 'FactTable' ),
           FILTER ( ALLSELECTED ( 'FactTable' ),
               'FactTable'[ReceivedDate] < D
               && NOT ISBLANK ( 'FactTable'[ClearedDate] )
               && 'FactTable'[ClearedDate] >= D
           ),
           REMOVEFILTERS ( 'DateTable' )
       )

       Check: Outstanding = Current + ClearedOn/After

       Outstanding As Of (check) :=
       [Currently Outstanding Before D] + [Cleared OnOrAfter D (from those received before D)]

       3) Your example (03/11/2025)

       • Received Before D (D = 03/11/2025): 17 rows (weeks 20/10 and 27/10).
       • Currently Outstanding Before 😧 2 (IDs 18 & 22 with blank ClearedDate).
       • Cleared OnOrAfter 😧 6 (e.g., IDs 14–17, 23, 24).
       • Outstanding As Of = 2 + 6 = 8 ✅

         4) Variations & tips

         • Include same-day receipts? If your definition is “on or before D”, switch the comparison to ReceivedDate <= D.
         • Axis by WC to see a trend: Place DateTable[WC] on the X-axis and use the same Outstanding As Of measure—its internal MAX('DateTable'[WC]) will evaluate per point on the axis.
         • Performance: If the table is large, create integer date keys (YYYYMMDD) and compare those inside the FILTER for faster scans.
         • Validation: Add a table visual with the three diagnostics to reconcile any differences end-to-end.

           5) Verified references (Microsoft Learn & trusted sources)

           • CALCULATE — Microsoft Learn
           • REMOVEFILTERS — Microsoft Learn
           • ALLSELECTED — Microsoft Learn
           • DAX Function Reference — Microsoft Learn
           • DAX Overview — Microsoft Learn
           • Modify DAX filter context (training module) — Microsoft Learn
           • REMOVEFILTERS — DAX Guide (SQLBI)
           • ALLSELECTED — DAX Guide (SQLBI)
             Next step: Drop these measures into your model and test with a WC slicer set to 03/11/2025. You should see 8. If anything differs in your real data (e.g., multiple date selections or alternative “as-of” rules), share a quick model screenshot and I’ll tailor the measure.

             ✔️ If my message helped solve your issue, please mark it as Resolved! 👍 If it was helpful, consider giving it a Kudos!