Forum Discussion

jb_karlsson's avatar
jb_karlsson
Frequent Visitor
5 years ago
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 over several years.

The parameters I have to work with is Billing frequency, billing start date(the first invoice date) and Billing end date(date of the last invoice) But I want to have a column that shows each planned invoice date between the start and end date that also follows the billing frequency. The table below is just an example.

 

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

3 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    5 years ago

    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: 

    https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

     

     

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago
    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}}),

    #"Billing Date" = Table.AddColumn(
        #"Changed Type",
        "Billing Date",
        each let 
                gap = (Date.Year([Billing End Date])-Date.Year([Billing Start Date]))*12+(Date.Month([Billing End Date])-Date.Month([Billing Start Date])),
                n=Number.IntegerDivide(gap, if Text.Contains([Billing Frequency], "Quarter") then 3 else if Text.Contains([Billing Frequency], "Semi") then 6 else 1)
            in List.Accumulate({0..n}, {}, (s,c) => s & {Date.AddMonths([Billing Start Date], if Text.Contains([Billing Frequency], "Quarter") then 3*c else if Text.Contains([Billing Frequency], "Semi") then 6*c else c)})
    )
in
    #"Billing Date"
  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    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.