cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Formula for a column to identify dates that are within 180 days from TODAY

I want to create a column that returns if a project will exprire within 180 days and I am not sure if I also need to account for it is expired or if it will fall from the calculation if it is or not.  Would I need "Expired", "Nearing Expiration", "Safe from Expiration"

If the permit start date is within 180 days from TODAY and the construction start date is blank it is "Nearing Expiration."  If the permit start date has passed (less than TODAY)  and the construction start date is blank it is "expired."  If the permit start date more than 180 days from TODAY, it is "Safe from Expiration."

Can I do this all on one column or do I have to add a column to calculate 180 days from TODAY as well?

 Permit Start Date Construction Start Date Do I need a column to calcualte 180 days from Today? Expired, Nearing Expiration, Safe from Expiration 6/24/2023 Expired 6/6/2025 Safe from Expiration 5/3/2024 4/1/2024 Safe from Expiration 7/15/2024 Nearing Expiration
1 ACCEPTED SOLUTION
Solution Sage

hello @lynnzrae ,

looking at your provided result, i assumed these categories :

- permit date less than today and blank construction date = expired

- permit date greater than today and within 180days and blank construction date = nearing expiration

- the rest would be safe from expiration

Expired, Nearing Expiration, Safe from Expiration =
IF(
VALUE('Table'[Permit Start Date])<TODAY() //find permit date less than today
&&
'Table'[Construction Start Date]=BLANK(), //check blank construction date
"Expired",
IF(
VALUE('Table'[Permit Start Date])>TODAY() //find permit date greater than today
&&
ABS(DATEDIFF('Table'[Permit Start Date],TODAY(),DAY))<180 //check if permit date and today are within 180days
&&
'Table'[Construction Start Date]=BLANK(), //find blank construction date
"Nearing Expired",
"Safe from Expiration"
))

Thank you.
3 REPLIES 3
Helper I

that worked, thank you.  I never would have figured that out on my own.

Solution Sage

Hello @lynnzrae

Thank you.

Solution Sage

hello @lynnzrae ,

looking at your provided result, i assumed these categories :

- permit date less than today and blank construction date = expired

- permit date greater than today and within 180days and blank construction date = nearing expiration

- the rest would be safe from expiration

Expired, Nearing Expiration, Safe from Expiration =
IF(
VALUE('Table'[Permit Start Date])<TODAY() //find permit date less than today
&&
'Table'[Construction Start Date]=BLANK(), //check blank construction date
"Expired",
IF(
VALUE('Table'[Permit Start Date])>TODAY() //find permit date greater than today
&&
ABS(DATEDIFF('Table'[Permit Start Date],TODAY(),DAY))<180 //check if permit date and today are within 180days
&&
'Table'[Construction Start Date]=BLANK(), //find blank construction date
"Nearing Expired",
"Safe from Expiration"
))

Thank you.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors