Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
OobuJoobu
Frequent Visitor

Calculate "days from now until" a date field, where sometimes the date will be blank

I'm trying to create a custom column called [Days Until Interview] in a table called 'Field Tracker' to tell me how many days from now until the date in a specific column called [Interview Date], but only where that date is today or in the future (I'm not interested where the date is in the past). The [Interview Date] column will not always be populated though. I would like the new column [Days Until Interview] to be null where the [Interview Date] is either not populated, or is in the past.

 

My logic would be for something like this:

 

In the 'Field Tracker' table, create a new custom column called [Days Until Interview]. If the [Interview Date] field is today or in the future, then how many days from now until then (0 in the case of where it's today's date) otherwise where the [Interview Date] field is blank or is in the past, show the result as null

 

 

Thanks for any help!

2 REPLIES 2
some_bih
Super User
Super User

Hi @OobuJoobu one of solution is to create calculaled column Days Until Interview (adjust your table name).

Create calendar table Date and connect it, in matrix use Date column from Date table and you will see only future days as shown on picture below.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Days Until Interview =
VAR _today=TODAY()
VAR _diff=DATEDIFF(_today,Sheet1[Interview Date],DAY)
VAR _result=
    IF(_diff<=0,BLANK(),_diff)
RETURN _result
 
some_bih_0-1688036697926.png

 

 

some_bih_1-1688036716745.png

 

some_bih_2-1688036725475.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






johnt75
Super User
Super User

You can use

Days Until Interview =
IF (
    'Field Tracker'[Interview Date] >= TODAY (),
    DATEDIFF ( TODAY (), 'Field Tracker'[Interview Date], DAY )
)

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors