cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kim_Sky
Helper II
Helper II

sum value from the time frame

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 dateEnd DateValue
1/6/202224/6/20221080
25/6/202215/7/2022900

 

Second Data 

DateValue
1/6/2022100
2/6/2022

200

24/6/2022150
28/6/2022175
3/7/2022181

 

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

 

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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"

View solution in original post

3 REPLIES 3
Vijay_A_Verma
Super User
Super User

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"

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

 

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"

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors