Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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)])
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
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)])
Thank You!!! I used the Date function and it worked.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |