Forum Discussion
Helper VDAX Newbie Count Rows by Date Filter
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]))
Super UserHi 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.
TxtcherThank 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.)
- 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:
DAXBacklog = 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?
- 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.