Calculate No of Days Between Previous Incident

[rahul_ferns]

Hi Experts

Need some help in creating a mesure to calculate the number of days between two lost time injury dates

I have managed to get the last LTI Date and the No of days since the last LTI (I have attached a test data table below)

What I need help with is:

Calculate the Previous LTI Date

Calculated No of the days between last LTI and the previous LTI

Regards

Rahul

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZJrT8IwFIb/SrPPmF6mXD4igUB0hLDFRZEPhVWtjJZ0XQj/3tOxhclFJFlOtmbPe8573s5mHvUx6WBGaMdreC8hlGedWRTJtUAj9Z2bHRwNuUrwQKpPYeCrl1sU61wl3rwx84iPKasEhl0oY8ENCmSWwXt/J6COjJGWW6kV0h/InTmSNjGhQDICv0QTKANpoHdXJqjHM3GlsU8wua/w+PU63l0YnsEMBc0YJg8VPb7gO8hTKzepkwo3hkuFQBCF1r3uLbQxaVYqJ+Yf+XJ1GaWYtCt08naExkZm9iILvovQDtbPDN9PF3rrtLmxkqcoEiqBACLost8ArYkUydcHGGjt+kdbmEPsF85amJIKCIMjYCh4crLkTpkwrTzelDBcLXhKPHi6Zbvg7IBOi2C0uou1WaGpSLmzBLOqVT3b95wQ1qyr+LSmcrKiKxm1yut5lg2tXvPl11/NGSlvqBOIopuXBxe89av///H5Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Employee = _t, #"Incident Type" = _t, #"Body Part" = _t, #"Type of Injury" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Employee", type text}, {"Incident Type", type text}, {"Body Part", type text}, {"Type of Injury", type text}})
in
    #"Changed Type"

[ryan_mayu]

@rahul_ferns 

you can try to create a column

Column = 
VAR _last=minx(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])&& 'Table'[Incident Type]="Lost Time Injury"),'Table'[Date])
return if('Table'[Incident Type]="Lost Time Injury" && NOT(ISBLANK(_last)),'Table'[Date]-_last)

[rahul_ferns]

Hi Ryan

I am getting 357 days from 04/09/2020 to date, it should be 339 days

[ryan_mayu]

@rahul_ferns 

a little confused abou this. based on the screenshot you provided. I can see three Lost time injury records.

2019/9/13, 2020/5/22, 2020/9/4. 

Why the result for the last record is 339? 

[rahul_ferns]

Hi Ryan

Appologies for the confusion

The last lost time injury date = 04/09/2020, I have calculated the no of days from the last LTI to today and thats 339 Days

What I need to calculate is the no of days between the previous lost time injury date (22/05/2020) and the last lost time injury date (04/09/2020). The number of days between these two dates should show as 105 days

I don't want to calculate any other dates even if there are any, as I am only interested in showing the current no days and the pervious no of days

Hope its clear

[ryan_mayu · Accepted solution]

@rahul_ferns 

pls see the attachment below

[rahul_ferns]

Thanks, Ryan

Worked just as I wanted

[ryan_mayu]

you are welcome

[rahul_ferns]

@ryan_mayu 

Can you please help me with a modification to the measure you provided and I accepted as a solution? Just worked out I need to exclude weekends

I have created a column in my Calendar table called IsWorkday 'Calendar'[IsWorkday] how do I modify the measure to exclude anything that is a 0 (0 is weekends and 1 is weekdays)

Previous LTI Record =
VAR _last=maxx(FILTER('Incidents_Data','Incidents_Data'[Incident Type]="Lost Time injury"),'Incidents_Data'[Date])
VAR _previous=MAXX(FILTER('Incidents_Data','Incidents_Data'[Incident Type]="Lost Time injury"&& 'Incidents_Data'[Date]<_last),'Incidents_Data'[Date])
return DATEDIFF(_previous,_last,DAY)

Days Since Last LTI = DATEDIFF([Last LTI Date], TODAY(),DAY)

How do I incorportae the IsWorkday into these measures?

Regards

Rahul

[ryan_mayu]

@rahul_ferns 

pls try this

Measure 2 = 
VAR _last=maxx(FILTER('Table (2)','Table (2)'[Incident Type]="Lost Time injury"),'Table (2)'[Date])
VAR _previous=MAXX(FILTER('Table (2)','Table (2)'[Incident Type]="Lost Time injury"&& 'Table (2)'[Date]<_last),'Table (2)'[Date])
return sumx(FILTER('Date','Date'[Date]>=_previous&&'Date'[Date]<=_last),'Date'[workday])

pls see the attachment below

[rahul_ferns]

Thanks Ryan

How do I measure from the last Lost Time Injury date to today?

Regards

Rahul

[rahul_ferns]

@ryan_mayu 

Did some more testing, the measure 2 includes both the start date and end date, is there a way to exclude the date the next incident happens (end date)

for example:

Previous Incident Date: 06/08/2021

Latest Incident Date: 09/08/2021

Should show days between these two incidents as 1 day because of the weekend in between and the 9th was the another incident

Regards

Rahul

[ryan_mayu]

@rahul_ferns 

for your first question, you can use the similar way to calculate. sum the weekdays from last injury date to today.

for your second question, will +1 or change the DAX to date<_last solve this problem?

[rahul_ferns]

@ryan_mayu 

Thanks for you Help

Much appreciated

[ryan_mayu]

you are welcome

[amitchandak]

@rahul_ferns , Try a new column in DAX

new column =
var _date = [Date]
var _IT =[Incident Type]
return
if([Incident Type] = "Lost Time Injury" , datediff(max(filter(Table, [Date] <_date && [Incident Type] =_IT && [Incident Type] = "Lost Time Injury" ),[Date]),[Date] , day), blank())

[rahul_ferns]

Hi Amit
Getting the below error

[amitchandak]

@rahul_ferns , Hope you are creating a new column. Not new measure

[rahul_ferns]

Hi Amit

Yes, I created a new column

[amitchandak]

@rahul_ferns , got the issue it need to MAXX not max

new column =
var _date = [Date]
var _IT =[Incident Type]
return
if([Incident Type] = "Lost Time Injury" , datediff(maxX(filter(Table, [Date] <_date && [Incident Type] =_IT && [Incident Type] = "Lost Time Injury" ),[Date]),[Date] , day), blank())

[rahul_ferns]

Hi Amit

That worked, now how do I create a measure to show the 105 days which is the previous total no of days?