All tickets created in a month, that was resolved in the same months

[FVal]

Hi,

Could one please help with creating a report based on two fields, Created Date and Resolved Date, that would show per month, how many tickets created in a month was resolved in that month?

The  report should display data like:

I have a report showing how many tickets were created each month, ex.:

January -100 tickets

February - 150 tickets

March - 200 tickets

I would need to have another, to show how many of those were resolved, but not how many were resolved in total, each month, only the onese that were also created within each month.

January - 30 tickets resolved (from that 100)

February - 120 tickets (from that 150)

March - 180 tickets (from that 200)

Thanks,

[v-yiruan-msft]

Hi @FVal ,

You can create one measure as below:

ClosedIncurrentMonth =
VAR a =
    MONTH ( FIRSTNONBLANK ( 'TicketInfo'[Created Date], TRUE () ) )
VAR b =
    CALCULATE (
        COUNT ( 'TicketInfo'[Ticket number] ),
        FILTER (
            'TicketInfo',
            MONTH ( 'TicketInfo'[Created Date] ) = MONTH ( 'TicketInfo'[Resolved Date] )
                && MONTH ( 'TicketInfo'[Created Date] ) = a
        )
    )
RETURN
    b

Best Regards

Rena

[FVal]

Hi Rena,

Thank you for your valuable input.

I did as sugested, nevertehless, I do see abit more values than it should be.

Ex. For a speficig group, there should be 41 Closed tickets, I checked in the application, then in the data source and 41 it's the correct number, but in the report it is displayed a total of 61 Closed tickets. 

Any idea where those extra ones are coming or how to filter it so it takes only the 41 ones?

Below the exact Measure, based on your example:

Val_ClosedIncurrentMonth =

VAR a =

MONTH ( FIRSTNONBLANK ( 'All_tickets_from_Fresh_API_Analytics'[Created Date], TRUE () ) )

VAR b =

CALCULATE (

COUNT ( 'All_tickets_from_Fresh_API_Analytics'[ ID]),

FILTER (

'All_tickets_from_Fresh_API_Analytics',

MONTH ( 'All_tickets_from_Fresh_API_Analytics'[Created Date] ) = MONTH ( 'All_tickets_from_Fresh_API_Analytics'[Resolved Date])

&& MONTH ( 'All_tickets_from_Fresh_API_Analytics'[Created Date] ) = a

)

)

RETURN

b

Regards,

Valentin

[v-yiruan-msft · Accepted solution]

Hi @FVal ,

Please update the formula as below:

Val_ClosedIncurrentMonth =
VAR a =
    MONTH (
        FIRSTNONBLANK ( 'All_tickets_from_Fresh_API_Analytics'[Created Date], TRUE () )
    )
VAR b =
    CALCULATE (
        DISTINCTCOUNT ( 'All_tickets_from_Fresh_API_Analytics'[ ID] ),
        FILTER (
            'All_tickets_from_Fresh_API_Analytics',
            MONTH ( 'All_tickets_from_Fresh_API_Analytics'[Created Date] )
                = MONTH ( 'All_tickets_from_Fresh_API_Analytics'[Resolved Date] )
                && MONTH ( 'All_tickets_from_Fresh_API_Analytics'[Created Date] ) = a
        )
    )
RETURN
    b

If the above measure still can't get the correct result, please provide some sample data(exclude sensitive data).

Best Regards

Rena

[FVal]

Hi @v-yiruan-msft,

Thank you for your support, the numbers are still the same even with the new measure.

I will have to check the source, even if the things looks fine there.

Could it count also rows where Closed date is empty, as there are some rows with no value there.

Thanks,

Val

[v-yiruan-msft]

Hi @FVal ,

Could you please provide some sample data for the following scenario in order to find the cause of getting incorrect return value? And it is better to provide your PBIX file if it is convenient. Thank you.

“Ex. For a specific group, there should be 41 Closed tickets, I checked in the application, then in the data source and 41 it's the correct number, but in the report it is displayed a total of 61 Closed tickets. ”

Best Regards

Rena

[v-yiruan-msft]

Hi @FVal ,

You can create one measure as below:

ClosedIncurrentMonth =
VAR a =
    MONTH ( FIRSTNONBLANK ( 'TicketInfo'[Created Date], TRUE () ) )
VAR b =
    CALCULATE (
        COUNT ( 'TicketInfo'[Ticket number] ),
        FILTER (
            'TicketInfo',
            MONTH ( 'TicketInfo'[Created Date] ) = MONTH ( 'TicketInfo'[Resolved Date] )
                && MONTH ( 'TicketInfo'[Created Date] ) = a
        )
    )
RETURN
    b

Best Regards

Rena

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Tad17]

Hey @FVal 

First off, make sure you have a calendar table as outlined here: https://powerbi.tips/2017/11/creating-a-dax-calendar/

I would create two calculated columns in your data that you can put in your visuals. The first would simply be a column with 1's to use as a "Count of tickets"

Count of Tickets = 1

Then you can create another column that will return "1" if the created date and resolved date are the same month:

Resolved in Same Month = IF(MONTH(Table[Created Date])=Month(Table[Resolved date]), 1, 0)

This will put a 1 in the column for each row where the created and resolved date share a month. If your data ranges over multiple years it will need to be:

IF(AND(MONTH(Table[Created Date])=Month(Table[Resolved date]),YEAR(Table[Created Date])=YEAR(Table[Resolved date]), 1, 0)

If this helps please kudo.

If this solves your problem please accept it as a solution.