items reopened in a month divided by items created in that month

[Anonymous]

i have a table with 3 columns TicketNumber, CreatedOn, Re-openedOn

the requirement is to Divide count(ticketnumbers) reopened with count(ticketnumbers) created in that month.

the tickets can be reopened in the later months as well.

TicketNumber, CreatedOn, Re-openedOn
1566                   Mar-21          May-21
1483                   Dec-20           Jan-21
1853                   Nov-20          Dec-20
1873                   Dec-20
1993                   Sep-20           Dec-20
1774                   Dec-20

what i need is (suppose data for Dec-20)
reopened in Dec: 2 : (1853,1993)
created in Dec: 3 : (1483,1873,1774)

Complaint_Rate = 2/3 = 66.67

but when i am using the following dax:
Divide(Count(reopenedOn),Count(createdOn))

and plotting it in a graph i am getting the value as 1/3 =33.34 

it is taking the values as (1483)(1)/(1483,1873,1774)(3 values)

can anyone suggest a suitable dax or suggest an alternative to get the required data.

[v-angzheng-msft · Accepted solution]

 Hi, @Anonymous 

Try to create a measure like below:

_Result =
VAR _a =
    EOMONTH ( MAX ( 'Table'[CreatedOn] ), 0 )
VAR _Re_openedOn =
    COUNTX (
        FILTER ( ALL ( 'Table' ), EOMONTH ( [Re-openedOn], 0 ) = _a ),
        [Re-openedOn]
    )
VAR _CreatedOn =
    COUNTX (
        FILTER ( ALL ( 'Table' ), EOMONTH ( [CreatedOn], 0 ) = _a ),
        [CreatedOn]
    )
RETURN
    IF ( _Re_openedOn = 0, 0, DIVIDE ( _Re_openedOn, _CreatedOn ) )

I created a simple sample to illustrate this.

Sample:

Result:

I created the index column for the month and year

_mm/yy = FORMAT ( 'Table'[CreatedOn], "mm/yy" )

Please refer to the attachment below for details

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

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

[MFelix]

Hi @Anonymous ,

The issue here is that you have two different date columns making the calculation incorrect.

Follow the steps below:

• Create a calendar table
• Active relationship with the CreateOn column and Date table
• Inactive relationship with the Re-openedOn column and Date table
• Add the following measure:

Reopened percentage =
DIVIDE (
    CALCULATE (
        COUNT ( 'Table'[Ticket Number] ),
        USERELATIONSHIP ( 'calendar'[Date], 'Table'[Re-openedOn] )
    ),
    COUNT ( 'Table'[Ticket Number] )
)

• Use the date table on your visualizations: