Forum Discussion

HabibAdil's avatar
HabibAdil
Helper IV
5 years ago
Solved

Missing values in calculated date column

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(...
Need Help
Tips and Tricks
  • selimovd's avatar
    selimovd
    5 years ago

    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
     
Arentir's avatar
Arentir
Resolver III
5 years ago

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