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

[Sithi1984]

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 file. This data has 5 columns and I want to get the Previous week's amount based on "Subscription Code" and "Product Category".

[#2]

@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.

[#3]

@mathetes Thanks for the quick response!

How can we achieve this using Power query (M Language)?

Any idea?

[#4]

@Sithi1984 

How can we achieve this using Power query (M Language)?

Any idea?

No idea. I've not used Power Query yet.

And I'm not sure why you'd need to use it, given that FILTER works just fine. You might be taking a sledge hammer to a task that is readily accomplished with a normal everyday hammer. Or, a "power tool" when an everyday manual tool works easily. Take your pick of analogies.

[#5 · Accepted solution]

@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.

[#6]

@SergeiBaklan @mathetes  Thank you both for the solution.

[#7]

@Sithi1984 , glad to help

[#8]

Hi @mathetes
FILTER function is not working for me, Can you please help me with some other approach?

[#9]

@rv3kk 

Help you with what? You jumped in on this thread "from the side," so to speak, a thread that had ended half a year ago. And you jumped in without any introduction as to what you're doing.

It would be better to start a whole new thread. And I would strongly suggest you consider helping others help you by posting a copy of your spreadsheet, the one where the "FILTER function is not working."

[#10]

ok