Power Query to calculate values based on previous 7 days values

[PSB]

I need Power Query to generate value in formula coulumn by below formula.

Each of the value is created with sum of previous 7 days values. Sample in table below.

Forumula column in Date 11/21 should caluculate below value based on data from 11/15 to 11/21

Formula = SUM(D2:D7)/SUM(E2:E7)

PERIOD_START_TIME	A	B	Formula
11/15/2023	534378867	16507726
11/16/2023	535142278	17353159
11/17/2023	550792555	16567704
11/18/2023	518398337	15864102
11/19/2023	472000935	15344195
11/20/2023	525670950	16507234
11/21/2023	554313263	16162058	31.96
11/22/2023	561912233	16593685	32.27
11/23/2023	497268678	15319635	32.80
11/24/2023	523602757	16238593	32.67
11/25/2023	502368984	16127418	32.60
11/26/2023	477062109	15513961	32.65
11/27/2023	476890668	15994182	32.48
11/28/2023	484104362	15747693	31.73
11/29/2023	495661575	15337546	31.19

[Ahmedx · Accepted solution]

pls try this

[PSB]

My ultimate goal is to achieve this formula. Do you suggest to add half of calculations to M1 and M2 or it can be added to Measure?

=100*((SUM(E10:E16)+SUM(F10:F16)-SUM(G10:G16)-SUM(H10:H16))/(SUM(D10:D16)+SUM(E10:E16)+SUM(F10:F16)-SUM(G10:G16)-SUM(H10:H16)))

[Ahmedx · Accepted solution]

pls try this

[PSB · Accepted solution]

My ultimate goal is to achieve this formula. Do you suggest to add half of calculations to M1 and M2 or it can be added to Measure?

=100*((SUM(E10:E16)+SUM(F10:F16)-SUM(G10:G16)-SUM(H10:H16))/(SUM(D10:D16)+SUM(E10:E16)+SUM(F10:F16)-SUM(G10:G16)-SUM(H10:H16)))

[Ahmedx]

where was it written that you want this is the formula, you didn’t write at the beginning

and this formula is incomprehensible, how do I know what it is

E10:E16

G10:G16

.....

[PSB]

@amitchandak can you please help? even DAX Query will do.