Forum Discussion

AJ1426's avatar
AJ1426
New Member
6 years ago

Days Since Incident Type (count)

I have the following DAX set up, I need it to release the days since last injury type specified. I have a table with the incident dates, and injury types

 

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 ()
)
Incident DateIncident Type
1/1/2020

Near Miss

2/4/2020Lost Time
2/5/2020

First Aid

3/5/2020Restricted Activity
3/5/2020Recordable
4/15/2020Lost Time
4/23/2020First Aid

 

I need help to get it to reflect the count of the days, but I am stuck

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    AJ1426 , The formula seems fine. What is the issue. The other way to try is

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    This strikes me as essentially MTBF. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586

     

    I would hazard a guess, but I think there is something missing OSHA Recordable, what is that I don't see it in the data.

     

    Anyway, ignoring that problem, with what you have presented, maybe:

    Days since last incident column = 
  VAR __Date = [Incident Date]
  VAR __PreviousDate = MAXX(FILTER('Sheet6',[Incident Type] = EARLIER([Incident Type]) && [Date] < EARLIER([Incident Date])),[Incident Date])
RETURN
  (__Date - __PreviousDate) * 1.

     

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi AJ1426 , 

    There is no Sheet6[OSHA Recordable] in sample, so if possible could you please inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.

    Please do mask sensitive data before uploading.

    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi

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