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

[mosarahit48]

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

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

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.

[mosarahit48]

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

[Ritaf1983]

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)

Result :

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

[mosarahit48]

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.

[Ritaf1983 · 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)

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