Forum Discussion
Create a live case count line graph
BA_Pete Looking at this it's not doing a live count it's just counting the number of cases opened for that specific time. What I'm trying to do(Not sure if it is possible in Power BI) is to count the number of cases open up until there is a completed date, something similar to this type of counting:
| Case ID | Start Date | Completed Date | Jan | Feb | Mar | Apri | May | Jun | Jul | Aug |
| 000001 | 05/02/2023 | 08/07/2023 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 0 |
| 000002 | 11/01/2023 | 14/03/2023 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
| 000003 | 18/04/2023 | 27/06/2023 | 0 | 0 | 0 | 1 | 1 | 1 | 0 | 0 |
| 000004 | 22/02/2023 | 10/10/2023 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 000005 | 08/03/2023 | 07/05/2023 | 0 | 0 | 1 | 1 | 1 | 0 | 0 | 0 |
| Count of open cases per month | 1 | 3 | 4 | 4 | 4 | 3 | 2 | 1 |
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
Yes, when setting the month/day as the x-axis the case completed that month/day will need to be counted as an open case until the next month/day.
Ok, I added another solution that should work for you then.
Pete