Desktop

[juansgutierrezu]

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.

I'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!!.

[MarcelBeug · Accepted solution]

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

[DoubleJ]

This thread might help:

https://community.powerbi.com/t5/Desktop/Cumulative-Total/td-p/43115

Greetings

JJ

[juansgutierrezu]

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]

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 )
    )

[Phil_Seamark]

This DAX Pattern has more detail on another approach

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

[MarcelBeug · Accepted solution]

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

[juansgutierrezu]

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]

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]

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]

this was the solution to this issue:

https://community.powerbi.com/t5/Desktop/Delta-Calculation-per-Time-Period-for-Group-Values/m-p/5027...