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