Forum Discussion
Solved
Calculating future invoice dates
I have a question fro calculating future invoice dates. I want to calculate future invoice dates in order to create an income forecast for subscription products. These subscriptions usually spans ove...
Hi jb_karlsson
Assume all invoice dates are the end dates of corresponding months, you can use below M codes to get the result.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PK8nIqVTSUTLXNzbUNzIwMgSyzfSNDUBsY6VYnWilwNLEopLUIrAqmAyyKhOwquDU3EwFx7y80sQcsEpDqHlGILYRlGOqFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Billing Frequency" = _t, #"Billing Start Date" = _t, #"Billing End Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Billing Frequency", type text}, {"Billing Start Date", type date}, {"Billing End Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Month Span", each (12 * (Date.Year([Billing End Date]) - Date.Year([Billing Start Date]))) + (Date.Month([Billing End Date]) - Date.Month([Billing Start Date]) + 1)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Billing Duration", each if [Billing Frequency] = "Monthly" then List.Numbers(0,[Month Span]) else if [Billing Frequency] = "Quarterly" then List.Numbers(0,[Month Span]/3,3) else if [Billing Frequency] = "Semi Annually" then List.Numbers(0,[Month Span]/6,6) else null), #"Expanded Billing Duration" = Table.ExpandListColumn(#"Added Custom1", "Billing Duration"), #"Added Custom2" = Table.AddColumn(#"Expanded Billing Duration", "Invoice Dates", each Date.EndOfMonth( Date.AddMonths( [Billing Start Date], [Billing Duration] ) )), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Billing Frequency", type text}}) in #"Changed Type1"You can remove the [Month Span] and [Billing Duration] columns after getting the invoice dates.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi jb_karlsson ,
please check this code or the attached workbook:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PK8nIqVTSUTIyMDLUNdc1NoSwjXXNdI0NlGJ1opUCSxOLSlKLEKrAMmC2CUJVcGpupoJjXmlpTiJUoZGuIdw4U11DIxAnNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Billing Frequency" = _t, #"Billing Start Date" = _t, #"Billing End Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Billing Start Date", type date}, {"Billing End Date", type date}}),
Start = List.Min( #"Changed Type"[Billing Start Date] ),
End = List.Max( #"Changed Type"[Billing End Date] ),
ListOfMonths = List.Generate(()=>[Date = Start], each [Date] <= End, each [Date = Date.AddMonths([Date], 1)], each [Date]),
ListOfQuarters = List.Generate(()=>[Date = Start], each [Date] <= End, each [Date = Date.AddQuarters([Date], 1)], each [Date]),
ListOfSemiAnnuals = List.Generate(()=>[Date = Start], each [Date] <= End, each [Date = Date.AddMonths([Date], 6)], each [Date]),
Custom1 = #"Changed Type",
#"Added Custom" = Table.AddColumn(Custom1, "List of Invoice dates", each if [Billing Frequency] = "Monthly" then List.Select(ListOfMonths, (x) => x >= [Billing Start Date] and x <= [Billing End Date]) else if [Billing Frequency] = "Quarterly" then List.Select(ListOfQuarters, (x) => x >= [Billing Start Date] and x <= [Billing End Date] ) else List.Select(ListOfSemiAnnuals, (x) => x >= [Billing Start Date] and x <= [Billing End Date] ))
in
#"Added Custom"
Next time when you post a question, please make sure to give your sample data in a readable format like described here: