Count of 'open' cases over time (Time & Date)

[Mehic_San]

Hi Everyone,

I'm currently working on a project where the end goal is to show the total count of 'open cases' at any given hour of a day.

Or better yet.. what is the count right now! everytime you refresh.

I have come a cross a lot of examples where the open cases are shown across dates, but not in combination with time. 

Unless you want to calculate working hours in between 🙂 , but that's not what i want!

Using all the information I can find.

This is what I got so far:

Open Tickets =

VAR MaxDate = MAX('Date'[Date])

VAR MinDate = MIN('Date'[Date])

VAR MaxTime = MAX('Time'[TimeKey])

VAR MinTime = MIN('Time'[TimeKey])

VAR CasesWithEnd =

Calculate(

Countrows(

FILTER(Projecten_PBI,

Projecten_PBI[Start_Date]<= MaxDate &&

Projecten_PBI[End_Date]>= MinDate &&

Projecten_PBI[TimeKey Start]<=MaxTime &&

Projecten_PBI[TimeKey End]>= MinTime)),

Crossfilter('Date'[Date],Projecten_PBI[End_Date],none),

Crossfilter('Time'[TimeKey],Projecten_PBI[TimeKey End],none))

VAR CasesWithNoEnd =

Calculate(

Countrows(

FILTER(Projecten_PBI,

Projecten_PBI[Start_Date]<= MaxDate &&

Projecten_PBI[End_Date]= BLANK() &&

Projecten_PBI[TimeKey Start]<=MaxTime &&

Projecten_PBI[TimeKey End]>= BLANK())),

Crossfilter('Date'[Date],Projecten_PBI[End_Date],none))

VAR CasesWithNoStart =

Calculate(

Countrows(

FILTER(Projecten_PBI,

Projecten_PBI[Start_Date= BLANK() &&

Projecten_PBI[End_Date]= BLANK())),

Crossfilter('Date'[Date],Projecten_PBI[End_Date],none))

VAR OpenCases = CasesWithEnd + CasesWithNoEnd - CasesWithNoStart

RETURN

OpenCases

When i run this formule my results are not exactly what i'm looking for.
When put into a graph, i get this picture:

There are 2 issues at the moment: 

1. Each new day the counting starts all over again including the open cases that have been created in the time slot that is shown.
So when we zoom into the time slot 00:00 - 01:00 on the 6th of december. It counts these cases:

The problem here is that it should include ALL the cases that have been opened before today and are still active. 
Instead of including only the tickets that have been opened before today in this time frame 00:00 - 01:00

2. The second issue is that tickets that are closed in the future (tomorrow or any other date) are not counted as an open case for today.

I have been wrestling with this the whole weekend and feel like the answer is right in front of me.

But i just can't see. 

Please help.

Kind Regards

Sandro

[amitchandak]

@Mehic_San , You can use logic in this blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

You have add time to Min and Max Date to create

example

max('Date'[Date]) -1 + time(13,0,0)  consider till 1 PM for today

[Mehic_San]

@amitchandak , thanks for the quick reply and effort. I tried something in the line of a current staff setup, but i still didn't get it to work. I am sorry but i don't understand your suggestion. 

[v-rzhou-msft]

Hi @Mehic_San 

According to your statement, I know you have three tables in your calculation, "Projecten_PBI", "Date", "Time".

You should have created relationships between "Projecten_PBI" and "Date"/"Time".

You want to calculate all cases include which have started before today but still active instead of only in time slot today. I think “still active” means case whose [End_Date] = blank(). Here I suggest you to try ALL() function to return the whole table in your calculate.

Like:

CasesWithNoEnd =
CALCULATE (
    COUNTROWS (
        FILTER (
            ALL ( Projecten_PBI ),
            Projecten_PBI[Start_Date] <= MaxDate
                && Projecten_PBI[End_Date] = BLANK ()
                && Projecten_PBI[TimeKey Start] <= MaxTime
                && Projecten_PBI[TimeKey End] >= BLANK ()
        )
    ),
    CROSSFILTER ( 'Date'[Date], Projecten_PBI[End_Date], NONE )
)

You can check your code by three part CasesWithEnd, CasesWithNoEnd and CasesWithNoStart. If three parts are all right, you can get correct result. 

If this reply still couldn't help you solve your problem, please share a sample with same data model like what you are dailing with. And you can show me a screenshot with the result you want to let me know what result you want. This will make it easier for more to understand your logic.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.