Forum Discussion
Date Calculation between three dates
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.
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])
4 Replies
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.
NathanielHi 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
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.
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])
