DAX Newbie Count Rows by Date Filter

[Txtcher]

I am using Excel Power Query / Data Model and I am brand new to DAX.

I have 3 queries:

Date_table

CasesSent

CasesClosed

The CasesSent table has 2 dates, Sent Date & Due Date.

The Cases Closed table has 1 date, Entrance Date.

I have created the following 1-to-many relationships with the Date_table[Date]:

CasesSent[Sent Date]

CasesSent[Due By]

CasesClosed[Entrance Date]

Question: Creating these relationships automatically filters the pivot table if I use the date fields from my date table, correct?

I am creating a power pivot to determine the following monthly totals:

How many Cases Sent

How many Cases Closed

How many Cases Due

How many Cases Overdue

The cases Due is the first issue. It gives me the same total as cases sent. So, I presume I need some kind of DAX calc to get this. Specifically, I need to count the number of cases with Due Dates that fall within the month = Date_table[Date].

Next is how to determine Overdue Cases:

A monthly count of the number of overdue cases (past the due by date and entrance date is null, or > Date_table[Date]).

Example:  Due Date=5/3/24. Entrance Date = 6/25/24.  This case needs to be included in a count when the Date_table is filtered for 6/1/2024.

From the little bit of studying I have done, I think this problem is known as an events in progress problem. And something  difficult for a newbie, but here I am.

Any assistance is greatly appreciated.

[Txtcher · Accepted solution]

Posting to state that after much research, I have solved the problem.  I have one date table that has multiple relationships with the other table. When creating formulas involving "inactive" relationships, I needed to active that relationship.

Example:  Cases Due:=CALCULATE(
COUNTROWS('RS Cases'),USERELATIONSHIP(Date_Table[Date],'RS Cases'[Due By]))

[Txtcher]

I have reviewed the measures created so far and NONE of them work except for the first one: Cases Sent and all that one does is count the rows without any filters.  All of the other measures do not work . Some of them get the first count correct, but the next month's count is completely wrong which is mind boggling. And this measure doesn't even get the first count correct:

Cases Closed:=CALCULATE(
COUNTROWS(
FILTER('RS Cases',
'RS Cases'[Entrance Date]>=Min(Date_Table[Date]) &&
'RS Cases'[Entrance Date]<=Max(Date_Table[Date])

)
)
)

Can anyone please help?

[FarhanJeelani]

Hi @Txtcher ,

You're on the right track, and yes, this is similar to an "events in progress" problem. Let's go through each of the DAX measures you'll need in Power Pivot to answer your questions. Since you have a `Date_table` and relationships set up, you can use that to filter your calculations.

1.  Count of Cases Sent
Since you have a relationship between `Date_table[Date]` and `CasesSent[Sent Date]`, you can create a simple DAX measure to count the cases sent within each month.

DAX
Cases Sent = COUNTROWS(CasesSent)

This measure will automatically count cases where `Sent Date` falls within the filtered date range from `Date_table`.

  2.  Count of Cases Closed
Similarly, with the relationship between `Date_table[Date]` and `CasesClosed[Entrance Date]`, you can count cases closed in each month.

DAX
Cases Closed = COUNTROWS(CasesClosed)

  3.  Count of Cases Due
For cases that are "Due," you need to count cases based on the `Due Date` in the `CasesSent` table, where `Due Date` falls within the current date filter. This is different from `Cases Sent`, so you’ll need a new measure:

DAX
Cases Due =
CALCULATE(
COUNTROWS(CasesSent),
FILTER(
CasesSent,
CasesSent[Due Date] >= MIN(Date_table[Date]) &&
CasesSent[Due Date] <= MAX(Date_table[Date])
)
)

This measure counts cases where the `Due Date` falls within the current filter context from the `Date_table` (usually a month).

  4.  Count of Overdue Cases
For overdue cases, you want to count cases where the `Due Date` has passed, and either the `Entrance Date` is blank (the case is not yet closed) or the `Entrance Date` is after the `Due Date`. To implement this, use:

DAX
Overdue Cases =
CALCULATE(
COUNTROWS(CasesSent),
FILTER(
CasesSent,
CasesSent[Due Date] < TODAY() && -- Check if Due Date has passed
(
ISBLANK(RELATED(CasesClosed[Entrance Date])) || -- Check if Entrance Date is blank (not closed)
RELATED(CasesClosed[Entrance Date]) > CasesSent[Due Date] -- or if Entrance Date is after Due Date
)
)
)

  Explanation of Measures

-  `Cases Sent` and `Cases Closed` : These count the rows in their respective tables based on the date relationship filters.
-  `Cases Due` : Counts rows in `CasesSent` where `Due Date` is within the month or date range selected in the `Date_table`.
-  `Overdue Cases` : Counts rows in `CasesSent` where:
- The `Due Date` is before today.
- Either the `Entrance Date` (from `CasesClosed` table) is blank (meaning the case is not yet closed), or the `Entrance Date` is after the `Due Date` (indicating it was closed late).

  Tips for Using These Measures in Power Pivot
- Make sure to use fields from the `Date_table` in your pivot table to filter by month, year, etc.
- If the calculations don't update as expected, confirm that your relationships between tables are set up correctly and that they are active.
- You might need to adjust the filter conditions if you have specific requirements on how overdue cases are calculated, especially for different time frames.

Please mark this as a solution , if its help you . Appreciate like on my post.

[Txtcher]

Thank you so much for the response. But that last expression is not working.

First, I changed my approach because I decided  to combine Cases Sent and Cases Closed together into one table which is now called RS Cases.

To calculate the cases overdue (backlog), I tried modifying the formula as follows but it gives me an error:

Backlog:=CALCULATE(
COUNTROWS(
('RS Cases'),
FILTER(
('RS Cases'),
'RS Cases'[Due By]<TODAY() && --Check if Due Date has passed
(
ISBLANK('RS Cases'[Entrance Date]) || --Check if Entrance Date is blank (not closed)
'RS Cases'[Entrance Date]>'RS Cases'[Due By] -- or if Entrance Date is after Due Date
)
)
)
)

Also is this going to provide a count of cases that were overdue depending on the date filter of the pivot? (See my example in my op.)

[Txtcher]

Ok:  I fixed the Backlog formula (syntax mistakes), but it is definitely not giving me the right counts.

The grand total for backlog intakes (overdue intakes) is 6796.

I am kind of lost here trying to come up with a way to fix it. I can't get my head wrapped around the point-in-time count.

[FarhanJeelani]

It looks like there's an issue with the way COUNTROWS and FILTER are combined in the formula. In addition, to ensure that the measure is dynamic and responds to the date filter in your pivot, you’ll need to include context based on the filter context of the Date table.

Here’s a refined version of your Backlog measure:

DAX

Backlog = 
CALCULATE(
    COUNTROWS('RS Cases'),
    FILTER(
        'RS Cases',
        'RS Cases'[Due By] < TODAY() && 
        (
            ISBLANK('RS Cases'[Entrance Date]) || 
            'RS Cases'[Entrance Date] > 'RS Cases'[Due By]
        )
    ),
    ALL('RS Cases'[Entrance Date])  -- This ignores the Entrance Date filter in the current context
)

[Txtcher]

Hummm... I am getting an error.  Here is what I wrote:

Backlog:=CALCULATE(

COUNTROWS(

FILTER(

'RS Cases',

'RS Cases'[Due By]<TODAY() && --Check if Due Date has passed

(

ISBLANK('RS Cases'[Entrance Date]) || --Check if Entrance Date is blank

'RS Cases'[Entrance Date]>'RS Cases'[Due By] --Check if Entrance Date is past Due Date

)

),

ALL('RS Cases'[Entrance Date]) -- This ignores the Entrance Date filter in the current context

)

)

The error is highlighting the ALL expression. Syntax error?

[FarhanJeelani]

HI  @Txtcher ,

Try This

DAX

Backlog:= CALCULATE(
    COUNTROWS(
        FILTER(
            ALL('RS Cases'), -- Remove all filters from the entire table
            'RS Cases'[Due By] < TODAY() && -- Check if Due Date has passed
            (
                ISBLANK('RS Cases'[Entrance Date]) || -- Check if Entrance Date is blank
                'RS Cases'[Entrance Date] > 'RS Cases'[Due By] -- Check if Entrance Date is past Due Date
            )
        )
    )
)

Changes made:

1. ALL('RS Cases'): Removed filters from the entire table instead of just the column. This is generally safer as it allows for broader context removal.

   Additional Considerations:

   • If you specifically want to keep filters on other columns while ignoring just the Entrance Date, you can adjust the logic depending on your needs.
   • If this doesn't resolve the error, please provide more details about the context in which this measure is being used, such as the specific error message or the model structure, so I can assist further!

[Txtcher]

I as getting closer with this, but it still is not right. 

In my table, I have a Case Sent Date.  So in order to get a rolling count of overdue intakes, the Sent Date has to be between the table date, and then the entrance date would be null, or greater than the max sent date.  But, the count is still not working quite right with the following formula. It gets me the correct grand total, but not the monthly count 😣

Backlog:=CALCULATE(
COUNTROWS(
FILTER(
'RS Cases',
'RS Cases'[Due By]<Max(Date_Table[Date] )&&
(
ISBLANK('RS Cases'[Entrance Date]) ||
'RS Cases'[Entrance Date]>Max(Date_Table[Date])
)
)
)
)

Also, the monthly count for cases due is not working correctly with this measure either:

Cases Due:=CALCULATE(
COUNTROWS(
FILTER(
'RS Cases',
'RS Cases'[Due By] >=MIN(Date_Table[Date]) &&
'RS Cases'[Due By] <=mAX(Date_Table[Date])
)
)
)

And I am trying to count the cases closed (if they have an entrance date, they are considered closed), but this is not giving me correct monthly counts either.

Frustrating! I am so close and I am completely stumped as to why this is not working.

[Txtcher]

Thank you again for your patience and prompt response.

Unfortunately, the measure did not give me the count I want.

I want a rolling count of the number of intakes that were not worked from month to month. For example, as of Nov 1, 2023, how many cases were overdue?  Then on Dec 1, 2023, how many overdue.

The measure when placed into the pivot table, did not provide the desired results: