Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors