Missing values in calculated date column

[HabibAdil]

Hi All,

Please can someone help why I am getting missing values in the calcualted date column (Next Calibration Date)?  I am using 

IF(ISBLANK(Gauge List[Last Calibration Date]),BLANK(),DATEADD(Gauge List[Last Calibration Date],Gauge_List[Calibration Frequency (Months)],MONTH))

The Last clibration date is also calcualted column from transcation table using MAXX and works fine.

Thanks, Habib

[Arentir]

Hi @HabibAdil ,

The DATEADD function return a table/value only if the date you return exist in the date table.

Here your date table only contains two values.

What you can do is create a date dimension that you will refer in your dateadd function. You can create a date dimension using CALENDAR or CALENDARAUTO for example

[selimovd · Accepted solution]

Hey @HabibAdil ,

the problem with DATEADD is, that it only returns values that exist in the mentioned column. It works well if you have a full and complete calendar, but not in cases like this.

You can either just use the DATE function and add the month to the calculated date:

Next Calibration Date = 
IF(
    ISBLANK( 'Gauge List'[Last Calibration Date] ),
    BLANK(),
    DATE( YEAR( 'Gauge List'[Last Calibration Date] ), MONTH( 'Gauge List'[Last Calibration Date] ) + 'Gauge List'[Calibration Frequency (Months)], DAY( 'Gauge List'[Last Calibration Date] ) )
)

Or you could already use AddMonths functiono in Power Query: 

Date.AddMonths([Last Calibration Date], [Calibration Frequency (Months)])

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[HabibAdil]

Thank You!!! I used the Date function and it worked.