DAX Newbie Count Rows by Date Filter

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.

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?