none
Previous Month Multiple Colums RRS feed

  • Question

  • Is there a simple way to go about comparing previous month values from column vs. current month values from a different column? For instance I have 5 columns: "Month", "Location", "Customer", "Nominations", "Deliveries". I'd like to compare previous month deliveries to current month nominations.

    Thanks for all the help!

    Tuesday, December 4, 2018 2:33 PM

Answers

  • Other option adapted from Colin's reply to Another join problem based on time range

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"Location", type any},
            {"Customer", type any}, {"Nominations", Int64.Type}, {"Deliveries", Int64.Type}}),   
        AddedColumn = Table.AddColumn(#"Changed Type", "ShiftedTable",
            (i) => Table.SelectRows(#"Changed Type",
                (j) => i[Month]-1 = j[Month]
            ), type table
        ),
        #"Removed Other Columns" = Table.SelectColumns(AddedColumn,
            {"Month", "Location", "Customer", "Nominations", "ShiftedTable"}),
        #"Expanded PreviousMonth" = Table.ExpandTableColumn(#"Removed Other Columns", "ShiftedTable",
            {"Deliveries"}, {"PrevMonth.Deliveries"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Expanded PreviousMonth",{{"PrevMonth.Deliveries", Int64.Type}})
    in
        #"Changed Type1"

    Friday, December 7, 2018 7:56 AM

All replies

  • Option to Merge query on Index from 0 and Index from 1

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,
            {{"Month", Int64.Type}, {"Location", type any}, {"Customer", type any},
            {"Nominations", Int64.Type}, {"Deliveries", Int64.Type}}),
        #"Sorted Rows1" = Table.Sort(#"Changed Type",{{"Month", Order.Ascending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows1", "Index", 0, 1),
        #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
        #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"},
                                             #"Added Index1", {"Index.1"},
                                             "MergeOnIndexes", JoinKind.LeftOuter),
        #"Removed Other Columns1" = Table.SelectColumns(#"Merged Queries",
            {"Month", "Location", "Customer", "Nominations", "MergeOnIndexes"}),
        #"Expanded Added Index1" = Table.ExpandTableColumn(#"Removed Other Columns1", "MergeOnIndexes",
            {"Deliveries"}, {"PrevMonth.Deliveries"})
    in
        #"Expanded Added Index1"

    Thursday, December 6, 2018 11:58 PM
  • Other option adapted from Colin's reply to Another join problem based on time range

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"Location", type any},
            {"Customer", type any}, {"Nominations", Int64.Type}, {"Deliveries", Int64.Type}}),   
        AddedColumn = Table.AddColumn(#"Changed Type", "ShiftedTable",
            (i) => Table.SelectRows(#"Changed Type",
                (j) => i[Month]-1 = j[Month]
            ), type table
        ),
        #"Removed Other Columns" = Table.SelectColumns(AddedColumn,
            {"Month", "Location", "Customer", "Nominations", "ShiftedTable"}),
        #"Expanded PreviousMonth" = Table.ExpandTableColumn(#"Removed Other Columns", "ShiftedTable",
            {"Deliveries"}, {"PrevMonth.Deliveries"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Expanded PreviousMonth",{{"PrevMonth.Deliveries", Int64.Type}})
    in
        #"Changed Type1"

    Friday, December 7, 2018 7:56 AM
  • If you have to apply it to very large tables and performance becomes an issue, you can use this method instead: https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/

    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Proposed as answer by Lz._ Friday, January 18, 2019 2:20 PM
    Sunday, January 13, 2019 9:45 AM
    Moderator
  • In case the OP would be interested… ;-)

    Hi Imke

    This question being raised regularly I took a bit of time to benchmark the above options against an Excel Table of 2 columns * 1 millions of rows. Used a pretty standard office laptop (Core I3/4Gb Ram), Win 10 x64, Excel 365 v1812 Monthly channel 32-bit

    Ran each query 3 times under the same conditions. Got quite consistent refresh time (before data loads into Excel) and no debate with this setup: your function is at least 20% faster than merging on indexes

    Definitively an option to advertise + like the flexibility your function offers. Great job!!! & Thanks for sharing it.
    If I could I would mark it answer, will Propose instead…

    THANKS again

    Friday, January 18, 2019 2:20 PM