sum value from the time frame

[Kim_Sky]

Hi

I have two set data one is start date and end date, second data need to sum between the start date and end date. 

First Data 

Start date	End Date	Value
1/6/2022	24/6/2022	1080
25/6/2022	15/7/2022	900

Second Data 

Date	Value
1/6/2022	100
2/6/2022	200
24/6/2022	150
28/6/2022	175
3/7/2022	181

I want to sum the data 2 in the time frame in data 1 and compare the both result. How should I do or what measure should I put?

Kind regards, 

Kim

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

Sample code for Table2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xcq5DQAgDAPAXVwTyTHvLij7rwHpEO3p9oYoGYfRUeAkojyoi/pQLWf/cCXO/uA01sTliDg=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}})
in
    #"Changed Type"

 Sample Code for Table1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1MNM1MFTSgXOMTIAcQwMLA6VYHYQSI1O4EnNdQxDH0gCoIhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Table2 Value", each List.Sum(Table.SelectRows(Table2, (x)=> x[Date]>=[Start Date] and x[Date]<=[End Date])[Value]))
in
    #"Added Custom"

[Kim_Sky]

Hi, for the code below, how do i change it to my own data list, like adding more value inside. 

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xcq5DQAgDAPAXVwTyTHvLij7rwHpEO3p9oYoGYfRUeAkojyoi/pQLWf/cCXO/uA01sTliDg="

 Thanks

[Kim_Sky]

let
Source = Excel.Workbook(File.Contents("xxx"), null, true),
IrishWaterBill_Table = Source{[Item="zzz",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(IrishWaterBill_Table,{{"Billing Period Start ", type date}, {"Billing Period End ", type date}, {"Days", Int64.Type}, {"Volume - m3", type number}, {"Water standing charge rate", type number}, {"Water supplied rate", type number}, {"Wastewater standing charge", type number}, {"Wastewater removed rate", type number}, {"Water Standing charge (Days*€1.36)", type number}, {"Water supplied ", type number}, {"Wastewter standing charge (Days*€1.4445)", type number}, {"Wastewater removed ", type number}, {"Total water service charge ", type number}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Billing Period Start ", "Billing Period End ", "Volume - m3", "Total water service charge "}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Table2 Value", each List.Sum(Table.SelectRows(WaterConsumption,(x)=> x[Date]>=[#"Billing Period Start "] and x[Date]<=[#"Billing Period End "])[MainIncomerWater])
in
#"Added Custom"