Find date between two identical values in another column

[Anonymous]

Hey

I want to find the difference in time (column: "occurred_at") between two identical numbers (column: "index") and print this in a new column.
An example is given below.

occurred_at	index	time_diff
10-01-2023 09:00:00	1	1 day
09-01-2023 09:00:00	1	(blank)
08-01-2023 09:00:00	2	2 days
07-01-2023 09:00:00	3	2 days
06-01-2023 09:00:00	2	(blank)
05-01-2023 09:00:00	3	(blank)

I really appreciate any help you can provide.

[AlB · Accepted solution]

Hi @Anonymous 

See it all at work in the attached file. Create a calculated column in your table:

time_diff = 
VAR min_ = CALCULATE(MIN(Table1[occurred_at]), ALLEXCEPT(Table1, Table1[index]))
VAR max_ = CALCULATE(MAX(Table1[occurred_at]), ALLEXCEPT(Table1, Table1[index]))
RETURN
IF(max_ = Table1[occurred_at], DATEDIFF(min_, max_, DAY))

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Idrissshatila]

Hello @Anonymous ,

If you have two date columns you can use datediff function to see the difference betweern the two dates.

To do so you add a new column with the following measure

Diff =
DATEDIFF ( Date1, Date2, DAY )

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[Anonymous]

I will clarify the question.
How do I know when to take the difference? 

[Idrissshatila]

@Anonymous,

Do you mean that you don't want to see blank ?

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[Anonymous]

I want to find the time difference between two identical numbers in the column "index". E.g. finding the time difference between the index-number 2 is 2 days (see table).