DAX formula to reference date of last event even if the latest date is not in the selected week

[nekalycam]

I thought this would be a trivial task, but I am stumped.  I have two tables that I am working with: one that has rows of safety incidents (OSHA recordables, first aid, etc.) and a date dimension.  The report I am designing will be weekly, meaning the user selects a week ending date and the report displays data "as of" that week (so could be a week in the past).  I need a DAX formula that pulls the last date a first aid event was recorded, and then show how many days it has been since that incident.  The problem I am having is the first aid incidents don't happen every week, so I want to pull the last first aid date even if it didn't happen in the week that is selected.

Example:
Week Ending 5/9/2020, there was a first aid event on 5/7/2020. This means when the user selects 5/9/2020 I want to show the number "2" as there have been two days since the last first aid event. However, when week ending 5/16/2020 is selected, there were no first aid events so it just pulls a blank value.  I want to pull that 5/7/2020 event and show that 9 days have now passed since the last event.

This is what my current DAX formula looks like:

Last First Aid = CALCULATE(LASTDATE('Safety Data'[Date]), FILTER('Safety Data', 'Safety Data'[Claim Type] = "First Aid"), FILTER(ALL(DimDate), DimDate[End of Week] <= MAX(DimDate[End of Week])))

Any tips much appreciated, thank you in advance.

[Anonymous · Accepted solution]

HI @nekalycam,

You can try below Mesure formula if this meets for your requirement:

Measure =
VAR currDate =
    MAX ( Calendar[Date] )
VAR lastEvent =
    CALCULATE (
        MAX ( 'safety incidents'[Date] ),
        FILTER ( ALLSELECTED ( 'safety incidents' ), [Date] < curDate )
    )
RETURN
    DATEDIFF ( curDate, lastEvent, DAY )

If above not help, please share some dummy data and expected results to test.

Regards,

Xiaoxin Sheng

[parry2k]

@nekalycam I have done a solution to one of my customers with the same requirement, looking for days since the last reported incident. 

Not sure how your tables are structured, if you share a sample data, it will be much easier. basically first find out last recordable incident, means remove all filter on your table

Last Incident = CALCULATE ( MAX ( Table[LastIncidentDate] ), ALL ( Table ) )

and use this date measure in your calculation to get the number of days, but if you provide sample data, it will be easy to put together a solution.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!