First time user so I apologise if I don’t provide enough info.
I’m looking to calculate the number of enquiry cases that would have been “open” at a certain point in time using the “Date Created” value and “date closed” value.
So for example, in the Date Created column, I have “19/01/2023 10:00 AM” and in the Date Closed column I have “25/01/2023 10:15 PM”.
So basically I want to be able to view the total number of cases that would have been open on a certain date/time. So in this case, the enquiry was considered as open between 10 AM 19th MAY - 10:15 PM 25th May. So if I were to check the total number of cases open on May 20th this would count as 1 to my total open cases.
Not sure if I’m on the right track but I’ve created a measure which checks the following:
Open Cases Count = COUNTROWS( FILTER( Enquiries, [Date Created] <= MAX('Date Table'[Selected Time]) && [Date Closed] >= MAX('Date Table'[Selected Time]) ) ) The values I’m getting are far too low. I expect this is because of the relationship of the created and closed columns with my calendar table?
1 Answer
I think you're missing an ALL()... removes the filters from a column or table.
Open Cases Count = COUNTROWS( FILTER( ALL(Enquiries), [Date Created] <= MAX('DimDate'[Date]) && [Date Closed] >= MAX('DimDate'[Date]) ) ) Without that, the relationship between your Calendar table and your Enquiries table is filtering for equality (DimDate[Date] = Enquiries[Date Created]), which isn't what you want.
1
