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