Point in time counts

[richrich123]

Evening,

I wonder if anyone could clarify the approach to this as I can't quote get my head around it. I use PBI to report of data (cases) in Dynamics Customer Service Hub. I have all the reporting I need on cases by resolution, by day, week, QTR, engineer who resolved etc as thats straight forward via the resolution date on the case.

The bit I can't quote get my head around is reporting on active (ie open) case by time. So as an example, today, Thursday, there are 156 open (active) cases, is that good or bad? How many were open last Thursday? Even better would be a chart to see how many were active/open last 7 days or last 30.

As an example, I can see cases by created date over last x days, I would like to be able to see total cases open by day like this but of course the case that was open last week now has a status of resolved so I guess it needs to be with the calanar looking at created and resolved dates and working out which were active/open on a given day and counting but thats where I am stuck. Any help with a measure would be much appreciated.

[richrich123]

@amitchandak , thanks for the link, thats helpful, I tried a few of those options but I can still only get counts of numbers by created date. Maybe this very simple Excel chart may illustrate what I am trying to do. The numbers are small just to keep the example simple to illustrate.

So as an example, I can see that today the count of all active cases = 15. What I want to see is how does 15 active cases today compare to whats typical. Is 15 a good or a bad number typically?

So based on knowing the date a ticket was created and the date is was resolved, I know that all the days in between created date and resolved date it was open/active. So I can see in this simple chart example that on the 27th, 9 were active. On the 4th July, 3 were active etc.

Hope that makes sense. Its that bottom line of active cases open each day that I am wanting to calculate.

[v-zhangti]

Hi, @richrich123 

Based on the data you provided, you can try the following methods.

Create a new date table.

Table:

Date = CALENDAR(MIN('Table'[Created On]),MAX('Table'[Resolved On]))

Measure:

Measure = 
CALCULATE (
    COUNT ( 'Table'[CaseID] ),
    FILTER (
        'Table',
        [Created On] <= SELECTEDVALUE ( 'Date'[Date] )
            && [Resolved On] >= SELECTEDVALUE ( 'Date'[Date] )
            && [Resolved On] <> BLANK ()
    )
)
    + CALCULATE (
        COUNT ( 'Table'[CaseID] ),
        FILTER ( ALL ( 'Table' ), [Resolved On] = BLANK ()
             &&[Created On] <= SELECTEDVALUE ( 'Date'[Date] ) )
    )

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[richrich123]

Hi @v-zhangti , thanks for your help with this. I created the new Table (had to be called date2 as I have a tabled called Date which is my calandar)

While this did work, the numbers aren't correct. It is returning daily numbers in the 700's but on a daily basis, the actual number of active cases are ~ 270 so its a long way out.

Just to confirm my actual Table and Measure based on your suggestion:

Table = 

Measure = 

This is returning this data:

The current Dynamics Advanced Find Query for all Cases where Status currently = Active is 277 which is a fairly typical number of cases active on any given day so the 700+ above is a long way out.

Many Thanks

[v-zhangti]

Hi, @richrich123 

You try changing COUNT to DISTINCTCOUNT. Counts the number of distinct values in a column.

If it still doesn't work, can you provide PBIX sample files for testing?

Best Regards,

Community Support Team _Charlotte

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

[richrich123]

Hi Charlotte, many thanks for your help again. I have changed to DISTINCTCOUNT but the numbers remain the same. With regards to sharing the pbix, I have to be very careful with that as its pointing to our live CRM and with Data protection that could casue a problem. Is there any way to share something that could help thats not going to compromise data protection?

Many thanks

[v-zhangti]

Hi, @richrich123 

You can create your own data in a format similar to your source data but not real data to use as an example.

Best Regards

[amitchandak]

@richrich123 , for open case refer

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

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.