0

I have this table:

Date Product Sales
1/3/2021 Apple 10
1/3/2021 Lemon 20
1/3/2021 Mango 30
1/4/2021 Apple 15
1/4/2021 Lemon 25
1/5/2021 Apple 20
1/5/2021 Lemon 20
1/5/2021 Mango 35

And I want to add a new column like this:

Date Product Sales Prev Day Sales
1/3/2021 Apple 10 null
1/3/2021 Lemon 20 null
1/3/2021 Mango 30 null
1/4/2021 Apple 15 10
1/4/2021 Lemon 25 20
1/5/2021 Apple 20 15
1/5/2021 Lemon 20 25
1/5/2021 Mango 35 null
dss333
  • 71
  • 1
  • 2
  • 7

1 Answers1

0

You could add a column with previousDate and then join the table itself by Date-PreviousDate fields, then extract only the Sales field an rename it at wish.

This should be null for days where no sales had taken place in the previous date.

Add a custom column with previous date as Date.AddDays([Date],-1) Then, in Home/Transform/Merge queries and select the same table to merge. Select the "Previous Date" field from the original and merge with the "Date" field on the second. Then CTRL-Click the field "Product" to use it also in the merge. Use a left outer join, so no rows are added. Expand the Sales column in the merged result and, here you go! Greetings!

Pablo Gómez
  • 16
  • 1
  • Could you elaborate more on how to do the join? Thanks! – dss333 Feb 24 '21 at 12:19
  • I tried to join the the table itself, but it didn't work since for each date there are more than 1 product. – dss333 Feb 24 '21 at 12:46
  • Of course! Add a custom column with previous date as Date.AddDays([Date],-1) Then, in Home/Transform/Merge queries and select the same table to merge. Select the "Previous Date" field from the original and merge with the "Date" field on the second. Then CTRL-Click the field "Product" to use it also in the merge. Use a left outer join, so no rows are added. Expand the Sales column in the merged result and, here you go! – Pablo Gómez Feb 24 '21 at 12:47
  • You have to merge using two fields. In the merge queries popup you can choose the second (and third, and fourth...) by ctrl-clicking any number of fields. In your example, dates and products are the required to obtain your result – Pablo Gómez Feb 24 '21 at 12:56
  • It worked! Could you edit your answer above (just copy-paste your comments), so that I can accept your answer and mark this question as solved? – dss333 Feb 24 '21 at 13:07