Create a live case count line graph

[Mr_Triongl]

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

[Mr_Triongl]

@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

[BA_Pete]

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

[Mr_Triongl]

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. 

[BA_Pete]

Ok, I added another solution that should work for you then.

Pete

[BA_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

[Mr_Triongl]

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?

[BA_Pete]

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

[Mr_Triongl]

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?

[BA_Pete]

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]

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