Forum Discussion

PSB's avatar
PSB
Icon for Helper III rankHelper III
2 years ago
Solved

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

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

ABCDEFFormula
11/15/202353437886716507726895664142754366021250.47 
11/16/202353514227817353159937350151015916096390.48 
11/17/202355079255516567704918460142561026270040.47 
11/18/202351839833715864102845614136447606006500.47 
11/19/202347200093515344195767703133815255050390.47 
11/20/202352567095016507234862003143874565744420.460.47
11/21/202355431326316162058912786138055146447490.470.47
11/22/202356191223316593685957433141797116636320.480.47
11/23/202349726867815319635818144131615845692190.480.47
11/24/202352360275716238593886929138976066120340.500.48
11/25/202350236898416127418838353140295165757140.470.47
11/26/202347706210915513961793928134804005444900.480.48
11/27/202347689066815994182862010137127206047100.530.49
11/28/202348410436215747693874205134680506042160.520.49
11/29/202349566157515337546875664130091715941400.520.50
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    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".

1 Reply

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    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".