Forum Discussion

_Roberto_'s avatar
_Roberto_
Frequent Visitor
6 years ago

Power BI - cases statistics

Hello to everyone !   I would like to create a report that shows statistics concerning opened, closed and active cases retrieved from CRM dynamics 365 based on weekly time range. In particular, I ...
Need Help
v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi _Roberto_ ,

 

We can try to use the following measures to meet your requirement:

 

Activetickets =
VAR lastDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( 'AllCases' ),
        FILTER (
            ALLSELECTED ( 'AllCases' ),
            AND (
                AND ( 'AllCases'[endDate] > lastDate, 'AllCases'[startDate] <= lastDate ),
                'AllCases'[statuscodename] = "Not Scheduled"
                    || 'AllCases'[statuscodename] = "In Progress"
                    || 'AllCases'[statuscodename] = "Pending Customer - Information"
                    || 'AllCases'[statuscodename] = "Pending Customer - On Hold"
                    || 'AllCases'[statuscodename] = "Pending Internal - Development"
                    || 'AllCases'[statuscodename] = "Pending Internal - Evaluation"
            )
        )
    )
ClosedTickets =
CALCULATE (
    COUNTA ( AllCases[ticketnumber] ),
    FILTER (
        ALLSELECTED ( 'AllCases' ),
        AND (
            'AllCases'[endDate] IN FILTERS ( 'Date'[Date] ),
            'AllCases'[statuscodename] = "Closed"
                || 'AllCases'[statuscodename] = "Problem Solved"
                || 'AllCases'[statuscodename] = "Canceled"
                || 'AllCases'[statuscodename] = "Duplicated"
                || 'AllCases'[statuscodename] = "Not A Bug"
                || 'AllCases'[statuscodename] = "Obsolete (Archived)"
                || 'AllCases'[statuscodename] = "Out of Scope"
        )
    )
)

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • _Roberto_'s avatar
    _Roberto_
    Frequent Visitor
    6 years ago

    Thank you v-lid-msft , I used measures that you suggested (I just modified "lastDate" in "lastDate1" because "lastDate" is a key word in power BI). But numbers diplayed in the chart are still not correct.

    I'm preparing a excel file so I can give you an example of real numbers retrieved from CRM.

    • v-lid-msft's avatar
      v-lid-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi _Roberto_ ,

       

      After checking the visual,  we found the error reason in my formula, when using weeknumber, it contains same week in multi years, can you put a filter year = 2018 and see if the formula can return the expected result?

       

      Edited, Sorry I did not see the selected year in the origin post, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please do not use any real data. Please upload your files to One Drive and share the link here.

       

      Best regards,

      Community Support Team _ Dong Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • _Roberto_'s avatar
        _Roberto_
        Frequent Visitor
        6 years ago

        v-lid-msft  thank you again for your support.

        I am sorry for the late reply, but maybe I found the issue.

        As I said data are retreived from a direct query from CRM dynamics database.

        In CRM I configured a workflow that is filling the column "closedon" by performing a copy of the value of "modifiedon" when the status reason of the a ticket is changed in "closed".

        So, I think that is matter of timing because probably the workflow is not syncronizing data in short time.

         

        I found this issue by performing an export of CRM data in the excel file (sorry if I didn't sent yet). Using the excel file all charts are correct.