Date Calculation between three dates

[Truecharv]

My data set has a creation date, reopend date, and closed date column. Not all lines have a reopen date. I am trying to create a column that will calculate the age based on the reopend date if it has a value, but the creation date in all other cases. Any help is appreciated.

[FreemanZ]

hi @Truecharv 

In addition to the measure proposed by Nathaniel, you may also create a column like this:

Age =
IF(
    [reopen date] <> BLANK(),
    DATEDIFF([closed date], [creation date]), DAY),
    DATEDIFF([reopen date], [creation date], DAY)
)

the result is in days, you may change DAY to other unit if necessary, like YEAR.

[Truecharv · Accepted solution]

This formula did not quite work but it was a great help to start testing. I was able to get the code to work with the following:

Age =

IF( 

[Reopen] <> BLANK (),

[Closed date]-[Reopen date],

[Closed date]-[created date])

[Nathaniel_C]

Hi @Truecharv ,

If you wish a measure, try this:

Duration =
IF (
    ISBLANK ( MAX ( dtaTable[Reopen Date] ) ),
    DATEDIFF ( MAX ( dtaTable[Creation Date] ), MAX ( dtaTable[Closed Date] ), DAY ),
    DATEDIFF ( MAX ( dtaTable[Reopen Date] ), MAX ( dtaTable[Closed Date] ), DAY )
)

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

Hi @Truecharv ,
Here is what my table looks like:

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel