Power BI - cases statistics

[_Roberto_]

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.

[v-lid-msft]

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_]

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]

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_]

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

[v-lid-msft]

Hi @_Roberto_ ,

So when the measure run, the enddate may be blank? If the enddate will be filled several times one day, do you have a logic about how to deal with this empty date? We can modify the formula to meet your new logic.

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.