Unbilled aging

[prashantmoger]

Hi,

Wonder if anyone can help, iam trying to create Unbilled aging report in power querry, which will show from which month onwards the project is becoming unbilled for invoice.   

For Example : In project 1, If we total the values from april to dec. Then from the  month of september the total of value  will become positive. So in next column the result should be September.  

Final result should be:

Thanks in adavance.

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sxLyUxU0lEKKMrPSk0uUTAEsh0LijJzgLRSrA52Bb6JlUBS18QACHAq8irNSwVSxgZmZuZ4FOWAjLIwNsapxLE0vbS4hICi4NSCktTcpNQiINvIEJ9K/+SSfIg6fKr88stgxuFT5pKaTIwyvAFqBA9QnNLQoDQ1MzfDowYckiYGloY41cCDEq8q5LC0NMOjEBGUOJUghSNONUiBaGpkAQzFWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Entity = _t, #"Project Name" = _t, Month = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entity", type text}, {"Project Name", type text}, {"Month", type text}, {"Value", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Entity", "Project Name"}, {{"Temp", each _, type table [Entity=nullable text, Project Name=nullable text, Month=nullable text, Value=nullable number]}}),
    fxProcess = (Tbl)=>
        let
            //Source = Table,
            //#"India_Project 1" = Source{[Entity="India",#"Project Name"="Project 1"]}[Temp],
            #"Added Index" = Table.AddIndexColumn(Tbl, "Index1", 1, 1, Int64.Type),
            #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Sum(List.FirstN(#"Added Index"[Value],[Index1]))),
            #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] > 0){0}
        in
    #"Filtered Rows",
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxProcess", each fxProcess([Temp])),
    #"Expanded fxProcess" = Table.ExpandRecordColumn(#"Invoked Custom Function", "fxProcess", {"Month", "Index"}, {"Month", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded fxProcess",{"Temp"}),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Removed Columns", {"Index"}, "Removed Columns", JoinKind.LeftOuter),
    #"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"Month"}, {"Unbilled from month"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Removed Columns",{"Index"})
in
    #"Removed Columns1"

[prashantmoger]

Thank you so much. Vijaya sir