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
mosarahit48
Frequent Visitor

How to create a custom column in Power Query for Expiration date of Products?

I have two columns. One column with "Purchase date" & Second column with "Subscription Type" which looks like this:

Purchase DateSubscription Type
09/01/2023Yearly
15/01/2023Monthly
02/02/2023Yearly
25/03/2023Monthly

The Output I am expecting for instance for first 2 rows needs to be this (For Yearly, 08/01/2024) & (For Monthly, 15/02/2023).
I want to add a custom column for calculating expiration date based on this two columns, which functions can I use to set it? or If someone can write a function for this particular case, it would be really greatful.

1 ACCEPTED SOLUTION

Hi again @mosarahit48  🙂
update your formula to :
Date.AddDays( if [Subscription Type] = "Yearly" then Date.AddYears([Purchase Date],1)
else
Date.AddMonths([Purchase Date],1),-1)

Ritaf1983_0-1683268054837.png

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

4 REPLIES 4
mosarahit48
Frequent Visitor

Thankyou @Ritaf1983 it works, Thank you for the help & support😀

Ritaf1983
Super User
Super User

Hi @mosarahit48 
You can add custom column with formula:
=if [Subscription Type] = "Yearly" then Date.AddYears([Purchase Date],1)
else
Date.AddMonths([Purchase Date],1)

Ritaf1983_1-1683258154597.png

Result :

Ritaf1983_2-1683258180123.png

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Hi @Ritaf1983 Thank you for presenting the solution. I greatly appreciate that.
However, I would like to see the expiration date as 1 day before, for instance: "for Yearly: "09/01/2023" to "08/01/2024" & "for Monthly: "28/02/2023" to "27/03/2023".
Could you further assist me with this.

Hi again @mosarahit48  🙂
update your formula to :
Date.AddDays( if [Subscription Type] = "Yearly" then Date.AddYears([Purchase Date],1)
else
Date.AddMonths([Purchase Date],1),-1)

Ritaf1983_0-1683268054837.png

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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.

Top Solution Authors