Count of resolved ticket for this month where ticket might have been created in next month

[ashaikh]

Hello,

Please help.

I have 2 ticket table, say Incident and Calls now these are independent tables and there is no relationship between. And I want to create Power BI report based on these two tables.

Since there is no relationship what I do is create a new table which has data from both table using union. I am looking to create monthly report so I use Start Time to filter record in Report Filter.

Now I want to see count of ticket Resolved in a particular month. Since I am using start time tickets created in previous month but resolved in current month are not included.

Is there a way I can do it, while also keeping the report filter I have. because there are around 12-15 pages in report and I cant have page level filter.

Thanks in advance.

[v-yulgu-msft · Accepted solution]

Hi @ashaikh,

Please add this measure into a card.

Count Resolved =
CALCULATE (
    COUNT ( Combined[TICKET ID] ),
    FILTER (
        ALL ( Combined ),
        Combined[STATUS] = "Closed"
            && Combined[END DATESTATUS].[MonthNo] = MAX ( Combined[MONTH] )
    )
)

Best regards,
Yuliana Gu

[v-yulgu-msft]

Hi @ashaikh,

To better illustrate your scenario, please provide more detailed sample data of both tables, Incident and Calls and screenshot of your desired output. Also, please show us how to determine whether a ticket is resolved.

Best regards,
Yuliana Gu

[ashaikh]

Hi @v-yulgu-msft

Here are the 3 tables

1. Incident

2. Work Order

3. Combined

Now as you can see the report level filter is from month column in Combined table.

Here if I select the filter as 8 (August) then the count i will get is for only those record highlighted as yellow. But other than the ones highlighted there are ticket created in previous month were also resolved in 8 - August and I would like to have that count as well.

Tickets are resolved based on the status - Closed

Desired output is just a count in a Card or Multi Card visual

Thanks

[CheenuSing · Accepted solution]

Hi @ashaikh

1. WHen you use the Month column from the combined it will only show the highlighted months, because of the filter context.

2. What you need to do is have a separate table of containing Year and Month like - 2017,01, 2017,2 etc... Call this as slicer table.

3. Use the Year and Month from this table as a slicers one for each.

4. For the meassure count write it as

 Count Resolved =
CALCULATE (
    COUNT ( Combined[TICKET ID] ),
    FILTER (
        ALL ( Combined ),
        Combined[STATUS] = "Closed"
            &&  Combined[END DATESTATUS].[Year] = Max( Slicer[Year] )
            && Combined[END DATESTATUS].[MonthNo] = Max( Slicer[MONTH] )
    )
)

If this works for you please accept this as a solutiona and also give KUDOS.

Cheers

CheenuSing

[ashaikh]

Thanks for the help.

Here is the discrepency I am facing. Under same condition which I spoke earlier if I use following code I get the correct output.

INCIDENT COUNT RESOLVED =
CALCULATE (
 DISTINCTCOUNT ( vRCCLCOMBINED[RCCL COMBINED ID] ),
 FILTER (
 ALL ( vRCCLCOMBINED ),
 ( vRCCLCOMBINED[STATUS] = "Closed"
 || vRCCLCOMBINED[STATUS] = "Resolved" )
 && vRCCLCOMBINED[END TIME].[MonthNo] = MAX ( vRCCLCOMBINED[MONTH] )
 && vRCCLCOMBINED[END TIME].[Year] = MAX ( vRCCLCOMBINED[END TIME].[Year] )
 && vRCCLCOMBINED[DATA ORIGINATE] = 3
 )
)

But when I try to use same logic in below code I get blank result. Could you suggest why it is like that.

INCIDENT SLA =
SUMMARIZE (
    vRCCLCOMBINED,
    vRCCLCOMBINED[PRIORITY],
    "SLA MET", COUNTX (
        FILTER (
            vRCCLCOMBINED,
            vRCCLCOMBINED[END TIME].[MonthNo] = MAX ( vRCCLCOMBINED[MONTH] )
                && vRCCLCOMBINED[END TIME].[Year] = MAX ( vRCCLCOMBINED[END TIME].[Year] )
                && vRCCLCOMBINED[DATA ORIGINATE] = 3
                && vRCCLCOMBINED[PROGRESS] = "Met"
        ),
        vRCCLCOMBINED[INCIDENT ID SLA]
    ),
    "SLA MISSED", COUNTX (
        FILTER (
            vRCCLCOMBINED,
            vRCCLCOMBINED[END TIME].[MonthNo] = MAX ( vRCCLCOMBINED[MONTH] )
                && vRCCLCOMBINED[END TIME].[Year] = MAX ( vRCCLCOMBINED[END TIME].[Year] )
                && vRCCLCOMBINED[DATA ORIGINATE] = 3
                && vRCCLCOMBINED[PROGRESS] = "Missed"
        ),
        vRCCLCOMBINED[INCIDENT ID SLA]
    ),
    "TICKET COUNT", COUNTX (
        FILTER (
            vRCCLCOMBINED,
            vRCCLCOMBINED[END TIME].[MonthNo] = MAX ( vRCCLCOMBINED[MONTH] )
                && vRCCLCOMBINED[END TIME].[Year] = MAX ( vRCCLCOMBINED[END TIME].[Year] )
                && vRCCLCOMBINED[DATA ORIGINATE] = 3
        ),
        vRCCLCOMBINED[INCIDENT ID SLA]
    ),
    "SLA %", COUNTX (
        FILTER (
            vRCCLCOMBINED,
            vRCCLCOMBINED[END TIME].[MonthNo] = MAX ( vRCCLCOMBINED[MONTH] )
                && vRCCLCOMBINED[END TIME].[Year] = MAX ( vRCCLCOMBINED[END TIME].[Year] )
                && vRCCLCOMBINED[DATA ORIGINATE] = 3
                && vRCCLCOMBINED[PROGRESS] = "Met"
        ),
        vRCCLCOMBINED[INCIDENT ID SLA]
    )
        / COUNTX (
            FILTER (
                vRCCLCOMBINED,
                vRCCLCOMBINED[END TIME].[MonthNo] = MAX ( vRCCLCOMBINED[MONTH] )
                    && vRCCLCOMBINED[END TIME].[Year] = MAX ( vRCCLCOMBINED[END TIME].[Year] )
                    && vRCCLCOMBINED[DATA ORIGINATE] = 3
            ),
            vRCCLCOMBINED[INCIDENT ID SLA]
        )
)

Any suggestion why it could be. Is there any change I need to do.

Thanks

[v-yulgu-msft · Accepted solution]

Hi @ashaikh,

Please add this measure into a card.

Count Resolved =
CALCULATE (
    COUNT ( Combined[TICKET ID] ),
    FILTER (
        ALL ( Combined ),
        Combined[STATUS] = "Closed"
            && Combined[END DATESTATUS].[MonthNo] = MAX ( Combined[MONTH] )
    )
)

Best regards,
Yuliana Gu