Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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!
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.