Sum based on date range

[Anonymous]

Hello, I am currently having an issue with calculating a sum based on the adjacent case in PowerQuery M.

I want to group the Column "Value" based on the the rows where Start_CW date and END_CW date is in between the of the range of columns Valid_from date and Valid_to date. 

Thanks in advance, 

best Regards

Index	CW	Start_CW	END_CW	Valid_from	Valid_to	Value
1	50	12/6/2021	12/12/2021	12/14/2021	12/25/2021	15
1	51	12/13/2021	12/19/2021	12/14/2021	12/25/2021	20
1	52	12/20/2021	12/26/2021	12/14/2021	12/25/2021	25

[edhans · Accepted solution]

None of your start/end dates are between the valid from/to dates, so nothing will be returned.

I added some rows where it was valid, but it returns that for everything.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI1ABKGRvpm+kYGRoYQNhAhcUyQOEamcI6pUqwO1AiYSmNkbZaEzTAyQJhhBJU0QFZpRoQZSO4whqpE0WZOhF8g7gA5wdQUmxkWhM0AOiMWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, CW = _t, Start_CW = _t, END_CW = _t, Valid_from = _t, Valid_to = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Valid_from", type date}, {"Valid_to", type date}, {"Value", Int64.Type}, {"Start_CW", type date}, {"END_CW", type date}}),
    GroupedSum =
        Table.AddColumn(
            #"Changed Type",
            "Grouped Sum",
            each
            List.Sum(
                Table.SelectRows(
                    #"Changed Type",
                    each [Start_CW] >= [Valid_from] and [END_CW] <= [Valid_to]
                )[Value]
            )
        )
in
    GroupedSum

But I am sure that is not what you want.

Can you provide some valid data, as well as an example of the expected output?

By the way, this will NOT perform well on large datasets. Power Query isn't set up to scan tables like this. A few hundred rows will be no problem A few thousand will be noticable. Over 10K-15K records and it will be pain. Over 100K and it won't finish. This is best done in DAX.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.