Count each day if case open

[NewbieJono]

Hello, how would i calculate if a case is open on a date. e.g the data shows case 1 was open during each day in April. case 2 case was open each day in April and may. i will also need to make a case each day if it is still open.

i basically want to show the live queue of cases.

i have a date table set up and its link to my table.

Below is a cut down snapshot

[rajendraongole1]

Hi @NewbieJono Create a measure to determine if a case is open on a given date

Hope you already created a seperate date table and modify the dates as start date and close dates as per your model design. currently i am using End date.

IsCaseOpen =
VAR SelectedDate = MAX('DateTable'[Date])
RETURN
CALCULATE(
COUNTROWS('Cases'),
FILTER(
'Cases',
'Cases'[StartDate] <= SelectedDate && (ISBLANK('Cases'[EndDate]) || 'Cases'[EndDate] >= SelectedDate)
)
)

to find the total open cases:

TotalOpenCases =
CALCULATE(
COUNTROWS('Cases'),
FILTER(
'Cases',
'Cases'[StartDate] <= MAX('DateTable'[Date]) && (ISBLANK('Cases'[EndDate]) || 'Cases'[EndDate] >= MAX('DateTable'[Date]))
)
)

The above measure display open cases for each month along with total opencases.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[NewbieJono]

Thanks for your help, but I'm not getting the output I was looking for. I want a line chart to show on the 1st of April, there were six active cases. And as the months go on, the cases would decrease as they get closed. By the time we hit July, there would only be 2 cases left.

but I would like to show this on a daily level. So the example dataset from April 1st to 5th will show 6 cases total, then it would drop to 5 on the 5th because one gets closed.

Sorry if this does not make much sense!

[rajendraongole1 · Accepted solution]

Hi @NewbieJono - Ok, To display the active cases, on dates can you create below measure.

ActiveCasesCount =
CALCULATE(
COUNTROWS(Cases),
FILTER(
Cases,
Cases[StartDate] <= MAX(DateTable[Date]) &&
(ISBLANK(Cases[EndDate]) || Cases[EndDate] >= MAX(DateTable[Date]))
)
)

Measure to Determine if a Case is Open on a Given Date, below flag helps and checks if a case is active on a particular date

Slight change 

IsCaseOpen =
VAR SelectedDate = MAX('Date'[Date])
RETURN
IF(
CALCULATE(
COUNTROWS('Cases'),
FILTER(
'Cases',
'Cases'[StartDate] <= SelectedDate &&
(ISBLANK('Cases'[EndDate]) || 'Cases'[EndDate] >= SelectedDate)
)
) > 0,
1,
0
)

Try the above approach for active cases and flag.  still issue exist, pls share some sample data.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[NewbieJono]

Actually, i think this was not working because I had a relationship set up. should I remove the link?

[DataNinja777]

Hi @NewbieJono 

Yes.  You should have the calendar table as a disconnected table for the flexible duration analysis to work.  

Best regads,