cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
juansgutierrezu Frequent Visitor
Frequent Visitor

Power Query previous row value based on criteria!! :O

Dear Power BI community: I would really appreciate if you could help me out with the following issue. I have a table with Customer ID and current month sales, but I would like to have another column with previous month sales for every ID. When I try to do it using an index column, it always calculates the sales from previous month, but it doesn't take into account the customer ID. I'll paste an example of what I'm trying to do above.

 

 

Example.pngI'm really new to Power BI and I have read about this topic many times, but I haven't still figured out how to deal with clustered indexes or maybe there's an easier way to achieve this. Thank you all!!.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
MarcelBeug Super Contributor
Super Contributor

Re: Power Query previous row value based on criteria!! :O

If you are looking for a Power Query solution, I have 2 alternatives.

In both cases I created an Excel workbook with your data; the step "Typed" is the actual starting point.

 

Alternative 1 is based on this blog by @MattAllington using indices and merge tables.

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Power Query previous row value based on criteria.xlsx"), null, true),
    SalesByCustomer_Table  = Source{[Item="SalesByCustomer",Kind="Table"]}[Data],
    Typed = Table.TransformColumnTypes(SalesByCustomer_Table,{{"Customer", type text}, {"Month", type text}, {"Current Month Sales", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(Typed, "Index1", 1, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index0", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index0"},#"Added Index1",{"Index1"},"Previous",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Customer", "Current Month Sales"}, {"Previous.Customer", "Previous.Current Month Sales"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Previous",{{"Index1", Order.Ascending}}),
    #"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Previous Month Sales", each if [Customer] = [Previous.Customer] then [Previous.Current Month Sales] else null ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"Customer", "Month", "Current Month Sales", "Previous Month Sales"})
in
    #"Removed Other Columns"

 

 

Alternative 2 is an alternative from myself.
A bit more complicated but I just like to share it as I think it might be interesting.

I created a function PreviousRecords that adds a null record as first row to a table, removes the last row of that table and turns the result into a list of records.

In the query I turn the original table into a list of records and create a table from this list and the list from the function.

 

Function PreviousRecords:

(SourceTable as table) as list =>
let
    Shifted = Table.Combine({#table(Value.Type(SourceTable),{List.Repeat({null},Table.ColumnCount(SourceTable))}),Table.RemoveLastN(SourceTable,1)}),
    Renamed = Table.TransformColumnNames(Shifted, each "Previous."&_),
    Records = Table.ToRecords(Renamed)
in
    Records

Query:

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Power Query previous row value based on criteria.xlsx"), null, true),
    SalesByCustomer_Table = Source{[Item="SalesByCustomer",Kind="Table"]}[Data],
    Typed = Table.TransformColumnTypes(SalesByCustomer_Table,{{"Customer", type text}, {"Month", type text}, {"Current Month Sales", Int64.Type}}),
    Records = Table.FromColumns({Table.ToRecords(Typed),PreviousRecords(Typed)}),
    ExpandedCurrent = Table.ExpandRecordColumn(Records, "Column1", {"Customer", "Month", "Current Month Sales"}, {"Customer", "Month", "Current Month Sales"}),
    ExpandedPrevious = Table.ExpandRecordColumn(ExpandedCurrent, "Column2", {"Previous.Customer", "Previous.Current Month Sales"}, {"Previous.Customer", "Previous.Current Month Sales"}),
    AddedConditionalColumn = Table.AddColumn(ExpandedPrevious, "Previous Month Sales", each if [Customer] = [Previous.Customer] then [Previous.Current Month Sales] else null ),
    RemovedOtherColumns = Table.SelectColumns(AddedConditionalColumn,{"Customer", "Month", "Current Month Sales", "Previous Month Sales"})
in
    RemovedOtherColumns
Specializing in Power Query Formula Language (M)
11 REPLIES 11
DoubleJ Member
Member

Re: Power Query previous row value based on criteria!! :O

juansgutierrezu Frequent Visitor
Frequent Visitor

Re: Power Query previous row value based on criteria!! :O

Thanks for answering DoubleJ, but that's not what I'm looking for, in that post they are trying to calculate a running total, instead, I'm trying to retrieve a previous row value based on a customer ID.

 

Thanks anyway.

Vvelarde Super Contributor
Super Contributor

Re: Power Query previous row value based on criteria!! :O

hi @juansgutierrezu

 

To solve this please follow these sequence:

 

1. Add a Index Column (Using Query Editor)

 

2. Create a calculated Column

 

Rank =
VAR Customer = Table1[Customer]
RETURN
    RANKX (
        FILTER ( ALL ( Table1 ), Table1[Customer] = Customer ),
        Table1[Index],
        ,
        ASC
    )

3. Create The PreviousRow Column Or in a Measure (Replace VAR Index = Table1[RANK]-1 with VAR Index=min(Table1[Rank])-1)  

 

PreviousRowSales =
VAR Index = Table1[Rank] - 1
RETURN
    CALCULATE (
        SUM ( Table1[Current Month Sales] ),
        FILTER ( ALLEXCEPT ( Table1, Table1[Customer] ), Table1[Rank] = Index )
    )

  




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Phil_Seamark Super Contributor
Super Contributor

Re: Power Query previous row value based on criteria!! :O

This DAX Pattern has more detail on another approach

 

http://www.daxpatterns.com/cumulative-total/


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
MarcelBeug Super Contributor
Super Contributor

Re: Power Query previous row value based on criteria!! :O

If you are looking for a Power Query solution, I have 2 alternatives.

In both cases I created an Excel workbook with your data; the step "Typed" is the actual starting point.

 

Alternative 1 is based on this blog by @MattAllington using indices and merge tables.

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Power Query previous row value based on criteria.xlsx"), null, true),
    SalesByCustomer_Table  = Source{[Item="SalesByCustomer",Kind="Table"]}[Data],
    Typed = Table.TransformColumnTypes(SalesByCustomer_Table,{{"Customer", type text}, {"Month", type text}, {"Current Month Sales", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(Typed, "Index1", 1, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index0", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index0"},#"Added Index1",{"Index1"},"Previous",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Customer", "Current Month Sales"}, {"Previous.Customer", "Previous.Current Month Sales"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Previous",{{"Index1", Order.Ascending}}),
    #"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Previous Month Sales", each if [Customer] = [Previous.Customer] then [Previous.Current Month Sales] else null ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"Customer", "Month", "Current Month Sales", "Previous Month Sales"})
in
    #"Removed Other Columns"

 

 

Alternative 2 is an alternative from myself.
A bit more complicated but I just like to share it as I think it might be interesting.

I created a function PreviousRecords that adds a null record as first row to a table, removes the last row of that table and turns the result into a list of records.

In the query I turn the original table into a list of records and create a table from this list and the list from the function.

 

Function PreviousRecords:

(SourceTable as table) as list =>
let
    Shifted = Table.Combine({#table(Value.Type(SourceTable),{List.Repeat({null},Table.ColumnCount(SourceTable))}),Table.RemoveLastN(SourceTable,1)}),
    Renamed = Table.TransformColumnNames(Shifted, each "Previous."&_),
    Records = Table.ToRecords(Renamed)
in
    Records

Query:

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Power Query previous row value based on criteria.xlsx"), null, true),
    SalesByCustomer_Table = Source{[Item="SalesByCustomer",Kind="Table"]}[Data],
    Typed = Table.TransformColumnTypes(SalesByCustomer_Table,{{"Customer", type text}, {"Month", type text}, {"Current Month Sales", Int64.Type}}),
    Records = Table.FromColumns({Table.ToRecords(Typed),PreviousRecords(Typed)}),
    ExpandedCurrent = Table.ExpandRecordColumn(Records, "Column1", {"Customer", "Month", "Current Month Sales"}, {"Customer", "Month", "Current Month Sales"}),
    ExpandedPrevious = Table.ExpandRecordColumn(ExpandedCurrent, "Column2", {"Previous.Customer", "Previous.Current Month Sales"}, {"Previous.Customer", "Previous.Current Month Sales"}),
    AddedConditionalColumn = Table.AddColumn(ExpandedPrevious, "Previous Month Sales", each if [Customer] = [Previous.Customer] then [Previous.Current Month Sales] else null ),
    RemovedOtherColumns = Table.SelectColumns(AddedConditionalColumn,{"Customer", "Month", "Current Month Sales", "Previous Month Sales"})
in
    RemovedOtherColumns
Specializing in Power Query Formula Language (M)
juansgutierrezu Frequent Visitor
Frequent Visitor

Re: Power Query previous row value based on criteria!! :O

Vvelarde, thanks for your model! It looks pretty cool!! unfortunately I'm planning to do this using Power Query, instead of doing this in eather a measure or in a calculated column, since I don't want to increase the size of the data model by creating more calculated columns and as far as I'm concerned it is better to perform this kind of calculations and custom columns in power query than in power pivot.

 

Thanks anyway, coo DAX patterns.

juansgutierrezu Frequent Visitor
Frequent Visitor

Re: Power Query previous row value based on criteria!! :O

Thanks Phil_Seamark but in that post they are trying to create a cumulative total, what I'm trying to perform is different. Thanks anyway!

juansgutierrezu Frequent Visitor
Frequent Visitor

Re: Power Query previous row value based on criteria!! :O

MarcelBeug, thanks a lot sir! You saved my life!. That's what I was trying to achieve. I'd rather use alternative No.1 since it seems easier and I'm not yet able with custom functions!!!. Thanks for your kind answer!.

tester Frequent Visitor
Frequent Visitor

Re: Power Query previous row value based on criteria!! :O

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 133 members 1,915 guests
Please welcome our newest community members: