Forum Discussion
Create a live case count line graph
It's giving you the open case balance as at the END of the axis time period:
The way that you've calculated your columns it looks like you want a case to be included in the open cases count for the month, even if it's been closed during that month. Is that correct?
Pete
To match the counting criteria that you've used for your columns, you would use this measure instead:
_noofOpenCases =
VAR __minDate = MIN(cal[date])
VAR __maxDate = MAX(cal[date])
RETURN
CALCULATE(
DISTINCTCOUNT(caseTable[Case ID]),
FILTER(
caseTable,
caseTable[Start Date] <= __maxDate
&& (__minDate < caseTable[Completed Date] || ISBLANK(caseTable[Completed Date]))
)
)
Which gives you this data shape:
Pete
I haven't got a count column in my table for each month.
The table in the previous comment was just an example of how I want the chart to count the the number of cases. Is there something else I need to do to the data to be able to do the count?
No, nothing else. Just set everything up how I've described and create whichever meaure gives the data shape/output that you want.
You'll just need to change the table and column references in your chosen measure to match those in your actual data model.
Pete
