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(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

 

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
     

3 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    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
     
    • HabibAdil's avatar
      HabibAdil
      Helper IV
      5 years ago

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

  • 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