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 would like to create a chart (line or clustered bar) that shows, for each week for a selected year, the number of opened tickets and the number of closed tickets, like the following one:

 

 

In order to do this, from a direct query issued on CRM database, I created a table 'AllCases' that contains all columns (including the custom ones). Then I created a calendar table in power BI as follow:

 

Then I created two custom columns in 'AllCases' table:

CreateDate = DATE(YEAR('AllCases'[createdon]),MONTH('AllCases'[createdon]),DAY('AllCases'[createdon]))
EndDate = IF(ISBLANK('AllCases'[closedon]),BLANK(),DATE(YEAR('AllCases'[closedon]),MONTH('AllCases'[closedon]),DAY('AllCases'[closedon])))
 
and I created two relationships between:
'AllCases'[CreateDate] -> 'Date'[DateNoTime] (active)
'AllCases'[EndDate] -> 'Date'[DateNoTime] (not active)
 
Then I created 3 measures:
Activetickets = CALCULATE(COUNTROWS('AllCases'),FILTER('AllCases','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('AllCases','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"))
Opentickets = CALCULATE(COUNTROWS('AllCases'), 
    FILTER('AllCases', ('AllCases'[createdon]<= LASTDATE('Date'[Date]) 
        && 'AllCases'[createdon]>= FIRSTDATE('Date'[Date]))))
and then I plotted in a bar chart using the following settings:
 
Obtaining the chart above.
The issue is that the tickets considered in this graph are the ones opened and closed the same week. So all those tickets opened in a certain week and closed in a different week are not considered. The same issue for activetickets, in particular I wuold like to have the total amount of active tickets for each week.
What can I do in order to have the correct number of tickets per week ?
 
Thank you in advance.
Need Help

5 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community 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
        Community 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.