topic Re: Get thet coresponding month value for last time accident happened in DAX Commands and Tips

Get thet coresponding month value for last time accident happened

Oelshamy — Sun, 28 Aug 2022 09:32:23 GMT

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

Re: Get thet coresponding month value for last time accident happened

ValtteriN — Sun, 28 Aug 2022 09:40:26 GMT

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/

Re: Get thet coresponding month value for last time accident happened

Oelshamy — Sun, 28 Aug 2022 09:44:59 GMT

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

Re: Get thet coresponding month value for last time accident happened

ValtteriN — Sun, 28 Aug 2022 10:06:52 GMT

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.

Re: Get thet coresponding month value for last time accident happened

Oelshamy — Sun, 28 Aug 2022 10:58:18 GMT

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

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 )

Re: Get thet coresponding month value for last time accident happened

Anonymous — Wed, 31 Aug 2022 02:04:26 GMT

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

Re: Get thet coresponding month value for last time accident happened

Oelshamy — Wed, 07 Sep 2022 04:58:03 GMT

Thank you very much ... worked perfectly as required

Appreciate it