Create a live case count line graph

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