Mathematical formula based on previous 7 days values. Prefer Power Query if not DAX will do too.

[PSB]

I need Power Query or DAX 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 =100*((SUM(E2:E8)+SUM(F2:F8)-SUM(G2:G8)-SUM(H2:H8))/(SUM(D2:D8)+SUM(E2:E8)+SUM(F2:F8)-SUM(G2:G8)-SUM(H2:H8)))

PERIOD_START_TIME	A	B	C	D	E	F	Formula
11/15/2023	534378867	16507726	895664	14275436	602125	0.47
11/16/2023	535142278	17353159	937350	15101591	609639	0.48
11/17/2023	550792555	16567704	918460	14256102	627004	0.47
11/18/2023	518398337	15864102	845614	13644760	600650	0.47
11/19/2023	472000935	15344195	767703	13381525	505039	0.47
11/20/2023	525670950	16507234	862003	14387456	574442	0.46	0.47
11/21/2023	554313263	16162058	912786	13805514	644749	0.47	0.47
11/22/2023	561912233	16593685	957433	14179711	663632	0.48	0.47
11/23/2023	497268678	15319635	818144	13161584	569219	0.48	0.47
11/24/2023	523602757	16238593	886929	13897606	612034	0.50	0.48
11/25/2023	502368984	16127418	838353	14029516	575714	0.47	0.47
11/26/2023	477062109	15513961	793928	13480400	544490	0.48	0.48
11/27/2023	476890668	15994182	862010	13712720	604710	0.53	0.49
11/28/2023	484104362	15747693	874205	13468050	604216	0.52	0.49
11/29/2023	495661575	15337546	875664	13009171	594140	0.52	0.50

[lbendlin · Accepted solution]

Your formula can be simplified.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RVNJbhwxEPvLnA249uUYwHbig+HAmdvA8P9/EUqakQ6NFqqrWSSLut0uf1+/3j9ffv5df31df67vH6+Xp8sLnvF+w/Mbz5/L99PtwvzM/iwkipKraVZF4szhlCmBY7VH2KiZpJuOWpCw+IaIA+HokqzRnurK3ji24kyj5kwo8YTo0N4QuSEwuMXdF4vIpDG8uSxosfBgkgEhSfj4gKgNwaVdqlOIV9hqL8OPU4iGWU60IILSDdEPCEshotbJAsYY9zjmoKMTQotdRs3J6QgR2izAM6mX7mGn6BheAeQJYVoJSqM1zUw2BB8vTFklZnsw/vSaXsDhmCyK3KemocgOC9kQwWgXXRDeGjVIN0aumnF28txIaOhhoduLRhAQi1peMPY2IIqLbdkJbl42p7XwYWHHC0Vk0le0RAtEBkShv5eQxkJmtFhI91LlpBOvqJ5jME/SeBAqLcRsCiFp52WnJx+IOEtNCmGaE+Gbdgzd2doyxakVGY2FORbSOxeSBwIcKGJ50Q0Scl8qz0VrgpqsaFnygdjptEIecY1kQiQQlxdpWO9iEVjrHUKg6AFx0jmuJP69p1NxLedNzcdNVWSXc4hzUDSw+P4P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"PERIOD_START_TIME", type date}, {"D", Int64.Type}, {"E", Int64.Type}, {"F", Int64.Type}, {"G", Int64.Type}, {"H", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Formula", each 100*(1-[D]/([D]+[E]+[F]-[G]-[H])), type number),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Last 7 Days", (k)=> let t =  Table.SelectRows(#"Added Custom1", each [PERIOD_START_TIME]<=k[PERIOD_START_TIME] and [PERIOD_START_TIME]>k[PERIOD_START_TIME]+#duration(-7,0,0,0)) in 100*(1-List.Sum(t[D])/(List.Sum(t[D])+List.Sum(t[E])+List.Sum(t[F])-List.Sum(t[G])-List.Sum(t[H]))),type number)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".