# How to calculate Reported late by number of days

Hi

Im hoping one of the many EDNA community PBI experts can help me on this one, I really wanted to try and work this out on my own but I’m on a deadline so need some help please

Here is the scenario. I have a report that provides insights into the Companies Health and Safety Management systems. The date the event is reported in the system is important to the company and the requirement KPI is that the event is reported in the system within 24 hours of the event occurring.

The Health and Safety management system records the date an event is recorded in the system “Created On” Date and the Date the Event Occurred - the “Occurred Date”.

To further complicate the scenario, the Company wants to alert the Health and Safety Team where an event is reported in the month following the event occurred and by how many days.

An example of what I am trying to achieve is in a table using conditional formatting highlighting two results, highlight in Orange where the “Created On” date is greater than the Occurred Date by more than 2 days and the second condition is Red when the Created On date is greater than the Occurred Date and the Created On date falls into the month following the “Occurred Date”

Any help to get me started on solving this problem would be much appreciated.

Thanks

Hi @AoakeP

I didnt understand the second condition but for first condition you can do something like this.

I assumed that you will be having an event table that has two date columns “Occured Date” & “Created Date”. Based on this assumption i have created a measure for the logic for greater than 2 days. You can modify it for the second condition or kindly upload a small sample data set and what you want to achieve for second condition. You can use excel too for your scenario results.

Also, here is the measure for you for greater than 2 days.
DateDiff Measure >2 Days =
VAR A =
‘Event Table’,
“Created_Date”, MAXX ( ‘Event Table’, ‘Event Table’[Created Date] ),
“Occured_Date”, MAXX ( ‘Event Table’, ‘Event Table’[Occurred Date] )
)
VAR B =
ADDCOLUMNS ( A, “DayDiff”, DATEDIFF ( [Occured_Date], [Created_Date], DAY ) )
RETURN
SWITCH ( TRUE (), MAXX ( B, [DayDiff] ) > 2, 1, 0 )

Happy to help.

Regards,
Hemant

1 Like

Hi Hemant @Hemantsingh

Thank you for responding so quickly, your solution looks good thanks.

I created a table in excel to demonstrate what I am trying to achieve in the second scenario, the rows where the Event column is highlighted in Red is where the Created On date falls in the month after the Occurred date month.

Hope this helps clarify what I am trying to acheive

ThanksConditional Format Date Calculation.xlsx (9.2 KB)

hi,

i guess for the second condition the below adjustment to the same formula should work.

here is the formula with second condition included

DateDiff Measure >2 Days & Following month =
VAR A =
‘Event Table’,
“Created_Date”, MAXX ( ‘Event Table’, ‘Event Table’[Created Date] ),
“Occured_Date”, MAXX ( ‘Event Table’, ‘Event Table’[Occurred Date] )
)
VAR B =
A,
“DayDiff”, DATEDIFF ( [Occured_Date], [Created_Date], DAY ),
“SameMonth”, MONTH ( [Created_Date] ) - MONTH ( [Occured_Date] )
)
RETURN
SWITCH ( TRUE (),
MAXX ( B, [DayDiff] ) > 2, 1,
MAXX ( B, [SameMonth] ) > 0, 2
)

1 Like

Wow thank you so much… I am going to try these both and I will let you know how I get on.

You have been a tremendous help and there would have been no way I would have figured that out in the timeframe I have.

Very much appreciated

2 Likes

Hi

I tried the calculation you provided but I got the below error when applying the measure to my report page.

I am not sure what I have done wrong. The table “Injury Table” exists in my model and in the table the Created On date and Occurred Date already exist, is there a way to apply a date diff calculation using the exisiting injury table without created a new table as a variable. Im trying to under what the logic is doing and why it is not working for me

Hi,

Yes it does. Can you confirm if you have converted “Created on” & “occured on” column to data type date??

Also,if you want to calculate the Day diff then use this measure.

DayDiff =
VAR A =
‘Event Table’,
“Created_Date”, MAXX ( ‘Event Table’, ‘Event Table’[Created Date] ),
“Occured_Date”, MAXX ( ‘Event Table’, ‘Event Table’[Occurred Date] )
)
VAR B =
A,
“DayDiff”, DATEDIFF ( [Occured_Date], [Created_Date], DAY )
)
)
RETURN
MAXX(B,[DayDiff])

Lemme Know how it goes.
Regards,
Hemant

@Hemantsingh

I will check data type is date, should it be date or date/time.

Also I’m curious on what purpose the Maxx function applies to this calculation?

Hi @AoakeP

MAXX is an iterative function means it will execute row by row in a table context. I have assumed that your dataset will have a unique eventid for each row. When you pick your eventid in a column in the table visual and put this measure on another column than for each row of eventid, MAXX will give you the max result for that eventid.

Check this video for more in depth understanding.

Regards,
Hemant