Forum Discussion

richrich123's avatar
richrich123
Icon for Helper III rankHelper III
4 years ago

Point in time counts

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 reporti...
Need Help
richrich123's avatar
richrich123
Icon for Helper III rankHelper III
4 years ago

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's avatar
v-zhangti
Icon for Community Support rankCommunity Support
4 years ago

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's avatar
    richrich123
    Icon for Helper III rankHelper III
    4 years ago

    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's avatar
      v-zhangti
      Icon for Community Support rankCommunity Support
      4 years ago

      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's avatar
        richrich123
        Icon for Helper III rankHelper III
        4 years ago

        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