Calculate % of total Incident that were reported within 48 hours

[MELO85]

Hi,

I need help in calculating the % of incidents reported within 48 hours .

My data looks like this :

data

I want my final result to be showing by country and by month the incident that were reported within 48 hours.

Please let me know if you need any more details

[danextian]

Hi @MELO85 ,

Please post a sample data? And if you say within 48 hours, what is that based on? Dataset refresh time?

[MELO85]

Hello,

Thank you so much for your reply.

Within 48 hours means the date diff between incident report date and created on dates.

Here's a sample data 

crc05_country_id	crc05_genderinvolved	crc05_incidentdate	crc05_incidentdatereported
2070	Woman	5/15/2022 0:00	6/9/2022 12:56
2037	Woman	6/6/2022 0:00	6/6/2022 19:49
2037	Man	6/8/2022 0:00	6/8/2022 15:04
2016	Man	6/6/2022 0:00	6/8/2022 14:46
2070	Man	5/3/2022 0:00	6/8/2022 11:01
2017	Woman	5/24/2022 0:00	6/6/2022 9:33
2037	Woman	5/24/2022 0:00	6/5/2022 23:15
2041	Woman	5/19/2022 0:00	6/5/2022 21:33
2037	Man	6/4/2022 0:00	6/5/2022 20:51
2014	Man	5/24/2022 0:00	6/5/2022 12:07
2049	Woman	6/1/2022 0:00	6/3/2022 17:28
2038	Woman,Man	5/23/2022 0:00	5/24/2022 14:06
2034	No personnel were impacted	5/18/2022 0:00	5/24/2022 14:03
2014	Woman	5/24/2022 0:00	6/1/2022 17:32
2037	Woman,Man	5/24/2022 0:00	5/24/2022 14:04

[v-xiaotang · Accepted solution]

Hi @MELO85 

Thanks for reaching out to us.

You can try this, create the measures below,

hourdiff = DATEDIFF( MIN('Table'[reported date]),MIN('Table'[incident date]),HOUR)

# of incidents = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[country id]))

# of incidents that were reported during 48 hours = CALCULATE(COUNTROWS('Table'),FILTER('Table',[hourdiff]<=48))

% of incidents that were reported during 48 = DIVIDE([# of incidents that were reported during 48 hours],[# of incidents])

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[danextian]

Hi @MELO85 ,

Id first create a calculated column compute the difference between the two datetime columns and then use that count how many incidents are within 48 hours.

calc column

Time Difference = 
DATEDIFF (
    'Table'[crc05_incidentdate],
    'Table'[crc05_incidentdatereported],
    HOUR
)

measure

Incidents within 48 hours = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER ( 'Table', 'Table'[Time Difference] <= 48 )
)

sample PBIX - https://drive.google.com/file/d/1r0uc0bzbyqe8-GCVG2Qbb4DfvLxCjUzj/view?usp=sharing 

[MELO85]

Thank you so much for your reply. This helps a lot.

I need the % of these incidents that happened during 48 hours out of the total by country.

Can I add the Countrows function by country . Do you have any idea how I can add that?

country id	# of incidents	# of incidents that were reported during 48 hours	% of incidents that were reported during 48 hours
country 1	6	2	33.33%
country 2	5	1	20.00%
country 3	4	1	25.00%