How to Calculate Duration of Days based on more than one Criteria?

[mosarahit48]

Purchase Date	Subscription Type	Expiration Date	Terminated at	Billing Date	Billing Type for Customer
09/03/2023	Yearly	08/03/2024		28/04/2023	Monthly
15/03/2023	Monthly	14/04/2023		28/04/2023	Monthly
02/03/2023	Yearly	01/03/2024	15/04/2023	28/04/2023	Monthly

Note:

1. Billing Date is maintained for each month.

2. "Terminated at" means "when the customer had choose to terminate the service of the product on that particular given date".

Now, I would like to calculate the Duration of days for issuing a invoice based on "Purchase Date" & "Billing Date". However, I would like to put 3 conditions:
1. if billing type is yearly than 0
2. if billing type is monthly than calculate the duration

3. if "Terminated at" date is given then calculate, "Purchase date+Billing Date" then "Billing date-Terminated Date", so it gives me the exact number of duration days.
Can you help me to formulate this logic in Power Query using custom column. I would appreciate for the help. @Ritaf1983 

[mosarahit48]

Thank you @m_dekorte , the logic was succesful implemented😀

[m_dekorte · Accepted solution]

Hi @mosarahit48 

Copy this into a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrDUNTDWNTJW0lGKTE0syqkEMgwswGImQCYQGQF5JhAVvvl5JRlAJbE60UqGpnCNMGEdJUMTuFrcOg2MsFhpCLcSZDBUFzYDYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Purchase Date" = _t, #"Subscription Type" = _t, #"Expiration Date" = _t, #"Terminated at" = _t, #"Billing Date" = _t, #"Billing Type for Customer" = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Purchase Date", type date}, {"Expiration Date", type date}, {"Terminated at", type date}, {"Billing Date", type date}}, "en-NL" ),
    AddDurationDays = Table.AddColumn(ChType, "Duration Days", each if [Billing Type for Customer] = "Yearly" then 0 else if [Terminated at] = null then Duration.Days( [Purchase Date] - [Billing Date] ) else Duration.Days( [Billing Date] - [Terminated at] ), Int64.Type)
in
    AddDurationDays

Amend to your needs.

Ps. If this helps solve your query please mark this post as Solution, thanks!