Time Function

[Absalon29]

I have a column showing bond maturity by date. Some of the bonds are perpetual (they no maturity date) and are denoted in the column as Perp. How do I calculate a column which gives me the Maturity date - Current date while excluding Perp rows. The column should show the number of months/years to maturity. 

[v-yulgu-msft]

Hi @Absalon29,

Based on my assumption, sample table looks like:

Please create acalculated column with below DAX:

Column =
IF (
    'bond maturity'[Perp] = "No",
    DATEDIFF ( TODAY (), 'bond maturity'[maturity date], MONTH ),
    BLANK ()
)

If I have something misunderstood, please share your sample data and post an image to describe your desired output.

Best regards,
Yuliana Gu

[Absalon29]

Thanks, attached is a snap shot of the portfolio data. There are two columns - Maturity and Next Call both of which have a combination of date format and text. I can ignore the rows with text but I want to be able calculate the time interval between maturity date and today. In the query editor I changed Maturity and Next Call to date formats but didn't appear to work. I then tried using the datediff function and got an error message saying start date (today) couldn't be greater than the end date. 

[v-yulgu-msft · Accepted solution]

Hi @Absalon29,

As you combined text value ("perp") and date value ("dd-mm-yyyy") in a single column [Maturity], its data type is forced to text. In that case, it is not possible to change it to date type. Either you cannot use DATEDIFF function, because datediff applies to date values.

In your scenario, one workaround is to filter out those rows containing "perp". Another workaround is to create a new calculated column like below, then, you can set the data type of [New Maturity] to date.

New Maturity=If(Table[Maturity]="perp",blank(),table[Maturity])

To calculate the time interval between maturity date and today, you can refer to:

Column = IF ( Table[Maturity] = blank(), blank(), DATEDIFF ( TODAY (), Table[Maturity], MONTH ),BLANK () )

Best regards,
Yuliana Gu