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.