topic Re: Formula for a column to identify dates that are within 180 days from TODAY in DAX Commands and Tips

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

lynnzrae — Tue, 02 Jul 2024 21:11:26 GMT

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

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

Irwan — Tue, 02 Jul 2024 23:23:27 GMT

hello lynnzrae ,

please check if this accomodate your need.

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"

))

Hope this will help you.

Thank you.

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

lynnzrae — Wed, 03 Jul 2024 10:34:38 GMT

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

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

Irwan — Wed, 03 Jul 2024 16:48:44 GMT

Hello lynnzrae

Glad to be a help.

Thank you.