Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Count Days Since last OSHA Incident

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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-2i5KdIB1S_837dK135IE4ANerwNvQ?e=tNc645

Best, 
Paul

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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 Anonymous !

     

    2018Count of OSHA RecordableCountdays between two Yes
    March37
    11 
    221 
    291 
    April211
    121 
    231 
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        Awesome this is great, thank you so much!