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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Adding column when Date is more than 21 days old

Hello.    I have a table names Contacts, and I would like to add a new column based upon the values of two other columns.   

If the column "Created Date" is older than 21 days from Today,  the new column should populate with "Expired".  If the column "Created Date" is not older than 21 days,  it should return the current value in the Status column.      

 

I have tried  IF ([CreatedDate] < TODAY() -21 Then "Expired" ELSE [Status])  and IF ([CreatedDate] < TODAY() -21, "Expired", [Status])

 

Any ideas?  

 

JohnBFH_0-1693491227947.png

 

Thanks in advance. 

1 ACCEPTED SOLUTION
ERD
Community Champion
Community Champion

@Anonymous , oh, you need it in PQ. I've provided the DAX. PQ:

if [CreatedDate] < Date.AddDays(DateTime.Date( DateTime.LocalNow()), -21) then "Expired" else [Status]

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

4 REPLIES 4
ERD
Community Champion
Community Champion

@Anonymous , what was wrong with your second try?

cln =
IF ( [CreatedDate] < TODAY () - 21, "Expired", [Status] )

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

ERD
Community Champion
Community Champion

@Anonymous , oh, you need it in PQ. I've provided the DAX. PQ:

if [CreatedDate] < Date.AddDays(DateTime.Date( DateTime.LocalNow()), -21) then "Expired" else [Status]

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Anonymous
Not applicable

I am recieving this error message 

JohnBFH_0-1693496238016.png

 

Anonymous
Not applicable

I'd personally make a datediff calculated column with your data table, then use a Switch with case when datediff < 21 then 'Current Table'[status] else 'Expired'.

You'll need to work out the proper syntax, but you get the idea

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.