Calculating historic counts based on two date columns

[August1987]

Hi,

I'm new to Power BI and struggling with something I feel should be relatively easy but I can't seem to get it to work.

The table below contains some dummy data as an example and my relationships are;

Active

DateTable : FactTable

Date 1 : * ReceivedDate

Inactive

DateTable : FactTable

Date 1 : * ClearedDate

ID	ReceivedDate	ClearedDate	ClearedFlag
1	10/11/2025		N
2	10/11/2025		N
3	10/11/2025		N
4	10/11/2025	10/11/2025	Y
5	03/11/2025		N
6	03/11/2025		N
7	03/11/2025		N
8	03/11/2025		N
9	03/11/2025	10/11/2025	Y
10	03/11/2025	03/11/2025	Y
11	03/11/2025	03/11/2025	Y
12	03/11/2025	03/11/2025	Y
13	03/11/2025		N
14	27/10/2025	10/11/2025	Y
15	27/10/2025	03/11/2025	Y
16	27/10/2025	03/11/2025	Y
17	27/10/2025	03/11/2025	Y
18	27/10/2025		N
19	27/10/2025	27/10/2025	Y
20	27/10/2025	27/10/2025	Y
21	27/10/2025	27/10/2025	Y
22	20/10/2025		N
23	20/10/2025	10/11/2025	N
24	20/10/2025	03/11/2025	Y
25	20/10/2025	27/10/2025	Y
26	20/10/2025	27/10/2025	Y
27	20/10/2025	27/10/2025	Y
28	20/10/2025	20/10/2025	Y
29	20/10/2025	20/10/2025	Y
30	20/10/2025	20/10/2025	Y

I'm trying to create a measure which will return a count of how many cases were oustanding on a filtered date. I believe this should be a count of the cases received before that date, that are also either currently outstanding (ClearedDate will be blank and ClearedFlag will display 'N') or have a ClearedDate of either on or after that date. Note, dates in the ReceivedDate and ClearedDate column are always a Week Commencing date and I'm using a WC column in the DateTable to filter.

Eg. if I filter for 03/11/2025, 17 cases were received before that date, of which 2 are currently outstanding, and 6 have a ClearedDate of either on or after that date so outstanding should be 8.

I hope that makes sense but if you need any clarification please let me know. Any help would be very much appreciated 🙂

[v-saisrao-msft]

Hi @August1987,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[v-saisrao-msft]

Hi @August1987,

Have you had a chance to review the solution we shared by @SolomonovAnton @GeraldGEmerick @PhilipTreacy ? If the issue persists, share the sample Pbix with the expected output so we can help to resolve the issue.

Thank you.

[PhilipTreacy]

Hi @August1987 

Download example PBIX file with code shown below

Your logic was a bit confusing but I think I've figured it out.

Based on this: Eg. if I filter for 03/11/2025, 17 cases were received before that date, of which 2 are currently outstanding, and 6 have a ClearedDate of either on or after that date so outstanding should be 8.

Then the 17 cases are ID's 14 through 30

The 2 outstanding are ID's 18 and 22

The other 6 are ID's 14, 15, 16, 17, 23, 24.

So filtering on 3 Nov 2025:

Note that a table will show 9 records because that's how many match the selected date

The measure is to calculate the Outstanding value is:

Outstanding = 

VAR _Outstanding_Before = CALCULATE(COUNTROWS(Data), FILTER(ALL(Data), 'Data'[ReceivedDate] < SELECTEDVALUE(Data[ReceivedDate]) && ISBLANK('Data'[ClearedDate]) && 'Data'[ClearedFlag] = "N"))

VAR _Cleared_After = CALCULATE(COUNTROWS(Data), FILTER(ALL(Data), 'Data'[ReceivedDate] < SELECTEDVALUE(Data[ReceivedDate]) && 'Data'[ClearedDate] >= SELECTEDVALUE(Data[ReceivedDate]) ))

RETURN _Outstanding_Before + _Cleared_After

Regards

Phil

[GeraldGEmerick]

@August1987 The sample data doesn't correspond to your specified results but I believe that something along the lines of following should suffice for your purposes:

Open Items = 
VAR _SelectedDate = SELECTEDVALUE( 'Table'[ReceivedDate] )
VAR _Table = FILTER( ALL( 'Table' ), [ReceivedDate] <= _SelectedDate && [ClearedDate] > _SelectedDate )
VAR _Return = COUNTROWS( _Table )
RETURN _Return

[SolomonovAnton]

 

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!

[August1987]

Thank you for your detailed response, I appreciate it so much.

When I've created the measure using my data I'm having a couple of issues. It returns a correct count of outstanding cases (currently 7765); however, when I add another field to the y-axis it shows 7765 for every item within that field. Also, when I add a slicer using the WC date and select a week, it returns a blank result.

Any ideas?

[PhilipTreacy · Accepted solution]

Hi @August1987 

This seems to be a separate issue to the original question i.e. you now mention a y-axis so there's a chart visual involved.

Have you tried my suggestion and checked my example PBIX file?

It's very hard to diagnose an issue when you don't supply the full dataset, or your PBIX file, or even some screenshots.  Please supply your PBIX file if you can, it'll make things much easier to help you.

Phil