Forum Discussion

Sithi1984's avatar
Sithi1984
Copper Contributor
Nov 17, 2020
Solved

How to add a column with previous week values in Power Query/Power Bi

I have a set of weekly data, I just want to add the previous week's amount next to current week's Amount. This will help me to find the difference between the weeks.   I have attached he sample fi...
BI & Data Analysis
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 17, 2020

    Sithi1984 

    You may merge the table with itself using Prior Week on left side and Week on right one , like

        #"Merged Queries" = Table.NestedJoin(
        #"Promoted Headers", {"Subscription Code", "Product Category", "Prior Week"},
        #"Promoted Headers", {"Subscription Code", "Product Category", "Week"},
        "Promoted Headers",
        JoinKind.LeftOuter
    ),

    Just expand Amount after that and apply desired cosmetic.

mathetes's avatar
mathetes
Gold Contributor
Nov 17, 2020

Sithi1984 

 

This formula seems to work. A quick and superficial review tells me that it's producing the result you want.

=FILTER($D$2:$D$111,($A$2:$A$111=A2)*($C$2:$C$111=C2)*($F$2:$F$111=G2),"")

 

FILTER does require the most recent release of Excel, so if it doesn't work for you that could be the reason. 

 

By the way, you posted only a CSV file. It's necessary to put this into an XLS or, preferably, XLSX format. as I've done with the attached.

 

Resources