Forum Discussion

richrich123's avatar
richrich123
Helper 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
v-zhangti's avatar
v-zhangti
Community 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
Helper 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
    Community 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
      Helper 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

      • v-zhangti's avatar
        v-zhangti
        Community Support
        4 years ago

        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