% Change calculation at row level

[Usamah22]

Hello,

I am trying to do some calculations on data that looks like below. I want to take the average of the latest 2 days of sales and compare it to the average of the previous 5 days. Trying to use a multi row formula. Can you help please?

Date	Sales
28/02/2020	9
29/02/2020	8
01/03/2020	10
02/03/2020	8
03/03/2020	9
04/03/2020	15
05/03/2020	22
06/03/2020	20

[RolandSchubert]

Hi @Usamah22 ,

you can your the Multi-Row Formula tool for that. Attached a sample workflow, hope this is helpful.

Best,

Roland

[Assaf_m]

Another approach using the sample tool.

[Usamah22]

Thanks, this is getting me somewhere. Just a bit more detail i should have mentioned before. I have different products in the data as below.

Product	Date	Sales
a	28/02/2020	9
a	29/02/2020	8
a	01/03/2020	10
a	02/03/2020	8
a	03/03/2020	9
a	04/03/2020	15
a	05/03/2020	22
a	06/03/2020	20
b	03/03/2020	55
b	04/03/2020	52
b	05/03/2020	100
b	06/03/2020	121

I need to get the latest % change and then do a cross tab which will make the latest % change a column as below. How do i only take the latest % change from the multirow?

Product	28/02/2020	29/02/2020	01/03/2020	02/03/2020	03/03/2020	04/03/2020	05/03/2020	06/03/2020	% Change
a	9	8	10	8	9	15	22	20	latest 5 days vs avg of previous
b					55	52	100	121

[RolandSchubert]

Had to changed the formula to limit calculation to the last available date. Then I added a Cross-Tab tool to switch rows to columns and a Join tool to add the "% Change", Dynamic Rename is to adjust the column headers. What do you think?

[Usamah22]

So close but need to tweak it. There are some products that have no sales for the most recent days. For example below there are no sales for 5 and 6 March so the multirow will take average of 3 and 4 march. I need it to take the average of 5 and 6 march even if there are no sales ie. be null.

Product	Date	Sales
a	28/02/2020	9
a	29/02/2020	8
a	01/03/2020	10
a	02/03/2020	8
a	03/03/2020	9
a	04/03/2020	15

[OllieClarke]

@Usamah22 how about this approach? (I gave two ways of displaying the null values)

edit: included the cross-tab

[RolandSchubert · Accepted solution]

Now this also should be covered (hopefully) ... let me know, if it works.