Forum Discussion
Create a live case count line graph
Hi, I've got a table that collects info on cases. I'm trying to create a line graph chart showing the number of cases active per month by creating new columns for each month and trying to create ...
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