cancel
Showing results for
Did you mean:

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.

Helper I

## Calculate Number of days cell is Blank

I need to calculate the number of days a cell has been blank (OTM #) based on the (Delivery Created On) date in my table and todays date. I am wanting to measure how long it takes a delivery to be assigned an OTM #. Once the OTM # is assigned I do not need a calculation. I just want to show "this delivery has been created on 3/25/2024 and it has been 2 days and still does not have an OTM#".

 Delivery Delivery Created On OTM # Days Blank 11266 3/25/2024 2 11267 3/26/2024 1 11268 3/25/2024 125 0
1 ACCEPTED SOLUTION
Community Support

May I ask if you have solved this problem? Many thanks to @lbendlin for providing the correct way to calculate the date interval, but if you don not know what to do next, you can try the MEASURE below.

Measure:

``````Days Blank1 =
VAR _day_diff =
INT ( TODAY () - 1 / 6 - SELECTEDVALUE ( 'Table'[Delivery Created On] ) )
RETURN
IF ( ISBLANK ( SELECTEDVALUE ( 'Table'[OTM #] ) ), _day_diff, 0 )
// Today is 4/1/2024``````

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

9 REPLIES 9
Community Support

May I ask if you have solved this problem? Many thanks to @lbendlin for providing the correct way to calculate the date interval, but if you don not know what to do next, you can try the MEASURE below.

Measure:

``````Days Blank1 =
VAR _day_diff =
INT ( TODAY () - 1 / 6 - SELECTEDVALUE ( 'Table'[Delivery Created On] ) )
RETURN
IF ( ISBLANK ( SELECTEDVALUE ( 'Table'[OTM #] ) ), _day_diff, 0 )
// Today is 4/1/2024``````

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Helper I

Thank you Yang

This worked the way I wanted it to.

Super User

INT(TODAY()-1/6-[Delivery Created On])

Super User

Please provide sample data that fully covers your issue.  Indicate the "current"  date for each row. Is it TODAY() ?
Please show the expected outcome based on the sample data you provided.

Helper I

The current date for each row is the present day. Below is how I want it to work. In the Days Blank column I want a formula to calculate the number of days the OTM # cell has been blank based on the Delivery Created on column. The data below is assuming the current date is 3/27/2024. The first delivery was created on 3/25/2024 and I want the formula to count the number of days the delivery has been created for without an OTM number. This value should be 2 since there are two days from 3/25/2024 and the current date (3/27/2024).

 Delivery Delivery Created On OTM # Days Blank 11266 3/25/2024 2 11267 3/26/2024 1 11268 3/25/2024 125 0 11269 3/27/2024 126 0
Super User

which timezone?  Remember that the Power BI Service runs on UTC.

Helper I

Eastern time zone

Super User

You would have to use TODAY() - 1/6  for that.

Helper I

So what is the entire DAX formula to calculate the number of days the cell has been blank?

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

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