cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## Calculate days between completion date and today with error for blanks

Hello

I'm trying to work out the days between 2 dates and have an error for blanks.

Currently I'm using this which calculates between the expiry date and today.
Expired days = value(TODAY()) - value('learning obj'[Actual Expiry Date])

This works fine until the expiry date is blank. How can I add in so that if its got no expiry date it says no expiry date instead?

1 ACCEPTED SOLUTION
Solution Specialist

I assume you are using a calculated column not a measure and it will be difficult to have different data types like numerical figures and text values. I would return a standard value in response to blank expiry date like 99999.

``````Expiry Days =
IF (
'learning obj'[Actual Expiry Date] = BLANK (),
99999,
TODAY () - 'learning obj'[Actual Expiry Date]
)``````

if you are ok with storing the days in text format then you can use a formula like this:

``````Expiry Days =
IF (
'learning obj'[Actual Expiry Date] = BLANK (),
"No Expiry Date",
FORMAT ( TODAY () - 'learning obj'[Actual Expiry Date], 0 )
)``````

2 REPLIES 2
Anonymous
Not applicable

Work perfectly.

Thank you 🙂

Solution Specialist

I assume you are using a calculated column not a measure and it will be difficult to have different data types like numerical figures and text values. I would return a standard value in response to blank expiry date like 99999.

``````Expiry Days =
IF (
'learning obj'[Actual Expiry Date] = BLANK (),
99999,
TODAY () - 'learning obj'[Actual Expiry Date]
)``````

if you are ok with storing the days in text format then you can use a formula like this:

``````Expiry Days =
IF (
'learning obj'[Actual Expiry Date] = BLANK (),
"No Expiry Date",
FORMAT ( TODAY () - 'learning obj'[Actual Expiry Date], 0 )
)``````

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors