CRM DB

Hi,

now my team is working on new project for the CRM SW CS Team.

need you help:

1. I would like to present in trend graph all cases created in certain month V closed cases at that month. how can i do it?

example: open case at 1.1.16 it was closed at the 3.1.16 the case status is now(january) closed - but it was also created in january.

2. once a case is closed its changes its status from open to closed -  I would like to have a possability to go back in time and see relevant case staus to that time for example:

for the 4th of january case number 123 status is closed, it was created in 1,12,16 . if i fillter by time and i look at december the case status should change back to open.

any ideas? need to finish this report ASAP.

now it looks like this:

Hi Noak,

Could you please post sample data from your tables so that we can provide you detailed DAX formulas?

In your scenario, you would need to create measures using COUNTROWS() function to calculate opened case count and closed case count. Then create a Line chart by dragging the measures into Values section and dragging Monthname field of your calendar table to Axis.

In addition, to filter your cases by time, you may need to create a calculate column to check the status of cases.

You can check the following similar blogs about how to calculate open cases.

https://blog.crossjoin.co.uk/2011/11/10/solving-the-events-in-progress-problem-in-dax-v2-0/
https://www.powerpivotpro.com/2013/04/counting-active-rows-in-a-time-period-guest-post-from-chris-campbell/

Thanks,
Lydia Zhang