CRM DB

[Noak]

Hi,

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

I realy need your help guys:

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:

 

[v-yuezhe-msft]

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-ca...

Thanks,
Lydia Zhang

[Noak]

Hi @v-yuezhe-msft

thank you for your replay, still stuck with this features. heres a screenshot of the data:

1. Right now if I look at the data all the open cases i see are relevant to Current time - NOW!, I want to see what was in the past I mean If CS SW Person opened 34 cases in October I want to see it with the status "Open" even if I look now i'll see only 4 open cases from October. 

2. I want to have graph shows all created cases in a certain month V closed cases how can I do it?.

this images relevant to 1:

This Images relevant to 2:

BR,

Noa.

[v-yuezhe-msft]

Hi @Noak,

The above graphs are your expected result, right? Could you please post sample data of your tables?

Thanks,
Lydia Zhang

[Noak]

Hi @v-yuezhe-msft,

My data connected to the SQL server (allot of records & columns).

here's a screenshot of what I basicly need to this specific graph.

(if i creat an excel chart the dates becomes numbers for some reason)

I can attach pbi template with demi records.

[v-yuezhe-msft]

Hi @Noak,

You can export data from the above table visual to CSV file, then upload CSV file to OneDrive and post shared link here. In addition, what is the result when you used the COUNTROWS function to create measures?

Thanks,
Lydia Zhang

[Noak]

Hi, @v-yuezhe-msft

after reading attached linkes you posted I would like to clarify some things:

1.viewing Open cases V Closed isn't my issue, the costumer need to see all cases(open+closed) in certain month V closed cases by using clustered column chart visual.

2. the costumer want to see the cases trend , I mean a garph that have 2 axis: x=months (13months), y= count of open\closed cases . the problem is that everyday the cases status is changing , cases status were last month open today might be close.

is there's any way to see the real data trend? 

[v-yuezhe-msft]

Hi @Noak,

You would need to create measure by summing open case count and closed case count, then create viusals using this measure and month field in calendar table.

For your second requirement, please help to post sample data of your tables and expected result so that we can provide you appropriate formulas.

Thanks,
Lydia Zhang

[Noak]

Hi @v-yuezhe-msft

"You would need to create measure by summing open case count and closed case count, then create viusals using this measure and month field in calendar table." - please provide function or explain.

---

@v-yuezhe-msft wrote:

Hi @Noak,

You would need to create measure by summing open case count and closed case count, then create viusals using this measure and month field in calendar table.

For your second requirement, please help to post sample data of your tables and expected result so that we can provide you appropriate formulas.

Thanks,
Lydia Zhang

---

[v-yuezhe-msft · Accepted solution]

Hi @Noak,

Please check if the attached PBIX file give your expected result.

Thanks,
Lydia Zhang

[Noak]

Thanks @v-yuezhe-msft

heres a link to the data:

DB Link

please provide a proper formula for my 1st issue as well , thank you.!.

[v-yuezhe-msft]

Hi @Noak,

I am not able to access your data via the above link. You can upload CSV file to OneDrive and post shared link of it via Private Message.

Thanks,
Lydia Zhang

[Noak]

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: