Forum Discussion
Calculating active cases by month
Hi! I have a data source that provides open and close date for cases. I need to be able to create a visual that shows how many ACTIVE cases were open in a month (so not only cases that opened that month, but anything that doesn't have a close date yet, and excluding anything that closed in a prior month).
I have a calendar table and then my data source looks something like this:
| Case ID | Open Date | Close Date |
| a | 12/5/2023 | 12/05/2023 |
| b | 10/27/2023 | |
| c | 5/16/2023 | 01/17/2024 |
| d | 05/06/2021 | |
| x | 01/16/2021 | 10/02/2023 |
| y | 09/29/2022 | 10/24/2023 |
| z | 07/26/2022 | 12/13/2023 |
The results should look something like this:
| October 2023 | 6 |
| November 2023 | 4 |
| December 2023 | 5 |
This is the forumula for the measure I tried and it gets me close but I'm still off by a few cases each month:
Open Cases =
VAR SelectedYear = SELECTEDVALUE('Calendar'[Year])
VAR MaxCDate = IF(ISBLANK(SelectedYear)||SelectedYear=YEAR(TODAY()),EOMONTH(TODAY(),0),MAX('Calendar'[Date]))
VAR X = CALCULATE([Handled Cases],
FILTER(ALL('Calendar'),
'Calendar'[Date]<=MAX('Calendar'[Date])
&&
MAX('Calendar'[Date])<=MaxCDate
),
'GAL Docket_Power BI'[Open Date]<=MaxCDate&&'GAL Docket_Power BI'[Close Date]>MaxCDate||ISBLANK('GAL Docket_Power BI'[Close Date])=TRUE())
RETURN X
Any thoughts on where I'm messing up??
Anonymous
pls try this
Measure = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Open Date]<=max('Table 2'[Date])&&('Table'[Close Date]>=min('Table 2'[Date])||ISBLANK('Table'[Close Date]))))pls see the attachment below
1 Reply
ryan_mayuSuper User
Anonymous
pls try this
Measure = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Open Date]<=max('Table 2'[Date])&&('Table'[Close Date]>=min('Table 2'[Date])||ISBLANK('Table'[Close Date]))))pls see the attachment below