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 something similiar to below to populate the values in each column month for the case.
Check Case Start Date, if it is <= March. If it's greater than this set the value to 0. But if it is <= March then check Case Completion Date to see if the date is => March then set value of 1.
So it should look like the below chart
| case ID | Case Start Date | Case Complete Date | January | Feb | March | April | May | June | July |
| 12345 | 01/03/2023 | 24/06/2023 | 0 | 0 | 1 | 1 | 1 | 1 | 0 |
| 10101 | 02/02/2023 | 05/05/2023 | 0 | 1 | 1 | 1 | 1 | 0 | 0 |
Is creating a new column for each month the best way to do this or is there a better way
10 Replies
Hi Mr_Triongl ,
Creating new columns for this is pretty much the worst way to do this.
All you need to do is:
-1- Create a calendar table - plenty of resources online on how to do this in Power BI.
-2- Send your table to the data model, ensuring you keep at least the [case ID], [Case Start Date], and [Case Complete Date] columns. Also send the calendar table if you've built it in Power Query (which I would recommend).
-3- Create a measure like this:
_noofOpenCases = VAR __cDate = MAX(calendar[date]) RETURN CALCULATE( DISTINCTCOUNT(yourTable[caseID]), FILTER( yourTable, yourTable[Case Start Date] <= __cDate && (__cDate < yourTable[Case Complete Date] || ISBLANK(yourTable[Case Complete Date])) ) )Now put any date column from your calendar table(date, month, year etc.) on the x-axis, and this measure on the y-axis, and it will show you the number of open cases at any given point in time.
As the measure uses MAX(calendar[date]) in the variable, it will tell you the open cases at the END of the axis time period. If you want it from the start, just change this to MIN(calendar[date]).
Pete
Thanks for the help and advice.
With this measure does it make a live count of cases per whatever calendar (day,month, year) you choose. Say for example will it show 10 cases open in June but in July 7 if the number has dropped?
Yes. Set it up as I've described and it will give you the open cases at each point in time based on the time period on the x-axis.
E.g. If you put calendar[Date] on the axis, it will tell you the number of open cases at the end of each date.
If you put calendar[Month] on the axis, it will tell you the number of cases that were open on June 30th, July 31st, August 31st etc.
Pete
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
