Get thet coresponding month value for last time accident happened

[Oelshamy]

I am working on a dataset having accidents data for 5 Years, 

I want to retrieve the last time an accident happened, which was 1 accident and it was March 2016, then i will calculate the days since that date.  I want to show it on a Card 

Date of Last Accident	March 2016
Days since last Accidents	2,344

I am not sure how the measure formula would be 

Appreciate your assistance or advice 

[Anonymous · Accepted solution]

Hi @Oelshamy ,

Please try this measure.

Measure = 
VAR _lastdate = MAX('HSE'[Date])
VAR _month = FORMAT(_lastdate,"MMMM YYYY")
VAR _days = DATEDIFF(_lastdate,TODAY(),DAY)
VAR _result = "Date of Last Accident " & _month & UNICHAR(10) & "Days since last Accidents " & _days & " days."
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Oelshamy]

Thank you very much ... worked perfectly as required 

Appreciate it 

[Oelshamy]

Hi @ValtteriN , 

I am not getting the same values when applying the values: 

Days = CALCULATE(DATEDIFF(Max(HSE[Lost Time Cases]),Today(),Day), All(HSE[Lost Time Cases ])) it shows 44500 days 

or

Days CALCULATE(DATEDIFF(Max(HSE[Date]),Today(),Day), All(HSE[Lost Time Cases ])) it shows 828

I am not sure what's wrong ... Also do you have the formula that gets the Value ( March 2016 ) 

[Oelshamy]

Thank you for your response, @ValtteriN  
What about getting the correspondent value that shows March 2016 as last time the accident happened, 

I am trying the below formula 

Last Occurence = LASTNONBLANKvalue(HSE[Date], LASTNONBLANK(HSE[Lost Time Cases ],1)) but it's not returning the month 

[ValtteriN]

Hi,

For getting the date/month you can modify the dax just a bit:

Date = CALCULATE(DATEDIFF(MAX('Table (7)'[Last accident]),ALL('Table (7)'[Last accident]))
Month  = CALCULATE(MONTH(MAX('Table (7)'[Last accident])),ALL('Table (7)'[Last accident]))

This will simply get the maximum value when the last accident happened.

[ValtteriN]

Hi,

Here is one way to do this:

Measure 19 = CALCULATE(DATEDIFF(MAX('Table (7)'[Last accident]),TODAY(),DAY),ALL('Table (7)'[Last accident]))

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/