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.
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 =
ADDCOLUMNS (
‘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 )
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
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
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.
