I am using the data model in Excel, not Power BI. And, I am brand new to DAX.
I have a data table and a calendar table.
The purpose of the report I am building is to track backlogged (overdue) cases.
Each case has the following dates and each of these fields has a relationship with the date table:
- Date Sent to Region
- Due By
- Entrance Date
- Status Change Date
Each case has a Status of either Open or Closed.
A case investigated timely would be: Status = Closed AND the Entrance Date <=Due By date.
The exception to this statement is that some cases are closed without an Entrance Date. In these instance we use the Status Change Date.
The goal is to create a monthly backlog table with a line chart. For example, how many cases were overdue in January 2024?
With the help of other users here, I created the following measure. Unfortunately, it produces a blank and not the count I need:
Backlog:=CALCULATE(
COUNTROWS(RS_Cases),
USERELATIONSHIP(Date_Table[Date], RS_Cases[Due By]),
FILTER(
ALL(RS_Cases),
(RS_Cases[Due By]<=MAX(Date_Table[Date]) && RS_Cases[Status]="OPEN") ||
(RS_Cases[Due By]<=MAX(Date_Table[Date]) && RS_Cases[Entrance Date]>Max(Date_Table[Date])) ||
(RS_Cases[Due By]<=MAX(Date_Table[Date]) && ISBLANK(RS_Cases[Entrance Date]) && RS_Cases[Status]="CLOSED" && RS_Cases[Status Change Date]>MAX(Date_Table[Date]))
)
)
I tried troubleshooting by breaking down each of the filters, and they all produce a blank. Whittled down to the minimum and it still produces a blank:
Copy of Backlog:=CALCULATE(
COUNTROWS(RS_Cases),
USERELATIONSHIP(Date_Table[Date], RS_Cases[Due By]),
FILTER(RS_Cases,RS_Cases[Due By]<=MAX(Date_Table[Date]) && RS_Cases[Status]="OPEN")
)
If I take out the "USERELATIONSHIP", then I get a semantic error that FILTER has been used in a True False expression:
Copy of Backlog:=CALCULATE(
COUNTROWS(RS_Cases),
FILTER(RS_Cases,RS_Cases[Due By]<=MAX(Date_Table[Date]) && RS_Cases[Status]="OPEN")||
(RS_Cases[Due By]<=MAX(Date_Table[Date]) && RS_Cases[Entrance Date]>Max(Date_Table[Date])) ||
(RS_Cases[Due By]<=MAX(Date_Table[Date]) && ISBLANK(RS_Cases[Entrance Date]) && RS_Cases[Status]="CLOSED" && RS_Cases[Status Change Date]>MAX(Date_Table[Date]))
)
I have been working on this for weeks now and have hit a wall. If it helps to explain what I want the measure to do, this is an Excel formula I created that calculates the results I need. It is based on a table with columsn for Month & Backlog Count. But it is not a dynamic solution and not the solution I am looking for:
=SUM(COUNTIFS(RS_Cases[Due By],"<="&EOMONTH([@Month]&1,0),RS_Cases[Entrance Date],">"&EOMONTH([@Month]&1,0)),
COUNTIFS(RS_Cases[Due By],"<="&EOMONTH([@Month]&1,0),RS_Cases[Entrance Date],"",RS_Cases[Status],"CLOSED",RS_Cases[Status Change Date],">"&EOMONTH([@Month]&1,0)),
COUNTIFS(RS_Cases[Due By],"<="&EOMONTH([@Month]&1,0),RS_Cases[Status],"OPEN"))
If more information is needed, please let me know. In the meantime, thank you in advance for getting this far into this post and your reply.
