Forum Discussion

Noak's avatar
Noak
Helper IV
9 years ago
Solved

CRM DB

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 m...
General Comment
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Noak,

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

    Thanks,
    Lydia Zhang

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

  • Noak's avatar
    Noak
    Helper IV
    9 years ago

    Hi Anonymous

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi Noak,

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

      Thanks,
      Lydia Zhang

      • Noak's avatar
        Noak
        Helper IV
        9 years ago

        Hi Anonymous,

        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.