Count Days Since last OSHA Incident

[Anonymous]

Hello, 

I'm stumped. I need help creating a count of days between "Yes" recordable events, and count of days since our most recent "Yes" OSHA Recordable. 

Date of Incident	Incident Type	OSHA Recordable
01/20/2019	Injury	YES
3/14/2019	Injury	NO
4/12/2019	Near Hit	NO
4/25/2019	Injury	YES

Thank you in advance! 

[Nathaniel_C]

Hi @Anonymous ,
Try this:

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Date Diff =
VAR _currDate =
    MAX ( injTable[Date of Incident] )
VAR _pastDate =
    CALCULATE (
        MAX ( injTable[Date of Incident] ),
        ALLEXCEPT ( injTable, injTable[OSHA Recordable] ),
        injTable[Date of Incident] < _currDate
    )
RETURN
    IF (
        MAX ( injTable[OSHA Recordable] ) = "YES",
        DATEDIFF ( _pastDate, _currDate, DAY )
    )

[V-pazhen-msft]

Hi,  

I assumed that your data has more than just the 4 rows. Basically you use DATEDIFF function:

1. Count days between the last yes and the second last yes 

1. Count days between the last yes and the second last yes 
Countdays between two Yes = 
var Lastyes = CALCULATE(LASTDATE(Sheet5[Date of Incident]),
                    FILTER(Sheet5,[OSHA Recordable]="YES"))

var Seclastyes = CALCULATE(LASTDATE(Sheet5[Date of Incident]),
                    FILTER(Sheet5,[Date of Incident]<Lastyes),
                        FILTER(Sheet5,[OSHA Recordable]="YES"))

Return DATEDIFF(Seclastyes,Lastyes,DAY)

2. Count days between the last yes and now.

Countdays since recent yes = 
var Lastyes = CALCULATE(LASTDATE(Sheet5[Date of Incident]),
                 FILTER(Sheet5,[OSHA Recordable]="YES"))

Return DATEDIFF([01Lastyes],NOW(),DAY)

There is the pbix if needed.

https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/ERdc7wQicIhEr9oL-2i5...

Best, 
Paul

[Anonymous]

Hello Paul, 

Thank you very much for this! When I plugged these in the "countdays between two yes" it only gives me the number of days between the last two OSHA recordables of the month. How could I expand this DAX to count every day between every yes? Again, thank you @V-pazhen-msft !

2018	Count of OSHA Recordable	Countdays between two Yes
March	3	7
1	1
22	1
29	1
April	2	11
12	1
23	1

[V-pazhen-msft · Accepted solution]

@Anonymous 

In this case, I would suggest you to create a column instead of a measure. See my sample below, hope it makes sense for you.

count since last yes =
IF (
    Sheet6[OSHA Recordable] = "Yes",
    DATEDIFF (
        CALCULATE (
            MAX ( [Date of Incident] ),
            FILTER ( Sheet6, [Date of Incident] < EARLIER ( Sheet6[Date of Incident] ) ),
            FILTER ( Sheet6, [OSHA Recordable] = "Yes" )
        ),
        [Date of Incident],
        DAY
    ),
    BLANK ()
)

Best,
Paul

[Anonymous]

Awesome this is great, thank you so much!