Reference Table To Sum Values In Date Range

[jtknowledge]

Hello,

I have 2 tables. One has a set of date ranges and the other has payment dates with the payment amount. I'm trying to add a column in the date range table that has the sum off all the payments that have a payment date that falls within that range.

Any ideas on how to do this?

[edhans]

Hi @jtknowledge ,

It depends on how much data you have. A few thousand records and this will work. Get into the hundreds of thousands or millions, and it can slow down.

You didn't provide any sample data to work with so I created my own. See links below if this doesn't work and you want to supply data.

I have data in this table, which needs to be called "Data"

Which can be created with this code - see below for links on how to use this code in Power Query.

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

Then I have this data:

The code below will take that table and add a Values column from the Data table (above) and turn it into the image below this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc7BCcAwDEPRXXIO2JJolwnZf42WFmL5JvQuf62BQDCZY77z/uee32+AJjRhEx1hXAUMZAGa0IQmqjB5mCxMLUwWpgrbDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),
    SumRange = 
        Table.AddColumn(
            #"Changed Type",
            "Values",
            each
                let
                    varStartDate = [Start Date],
                    varEndDate = [End Date]
                in
                List.Sum(
                    Table.SelectRows(
                        Data,
                        each [Date] >= varStartDate and [Date] <= varEndDate
                    )[Values]
                )
        )
in
    SumRange

My full PBIX file is here.

If you want to share some sample data, see below:

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[jtknowledge]

Hi edhans,

I tried using that, but I was having trouble with making it work with the datasource I'm using. I tried adding it to the existing query and trying to figure out how to change the names in the query for my tables, but couldn't get it to work. I'm using two excel spreadsheets one called data that has the dates and payments and another that that has the date ranges.

I used a transform in the data spreadsheet to convert the dates and time to just dates. I had to use onedrive with the spreadsheets as it gives me an html error if I try and put it in the post.

Data spreadsheet: https://booscpa-my.sharepoint.com/:x:/p/jtoews/EZJ0UBIAZMdLhloupYnEN6sBJ_zouXxDeJuYMGc0WPVqbg?e=G2ib...

Date spreadsheet: https://booscpa-my.sharepoint.com/:x:/p/jtoews/EXzQABuYmZ5PpoawXhzu4bABaX3GMVg552gCHGyIBKboBg

Please let me know what I can do with this.

[edhans · Accepted solution]

@jtknowledge See the file I linked to above. I added your data (just copy|pasted a sample of the data) in New Data and New Date Ranges. You can see the M code there.

Note that the data types have to be the same. You have DateTime in your values file, and Dates in your date range. So either:

1. Convert your DateTime to date, which strips out the time stamp, or
2. Convert your Date to datetime, which adds an arbitrary 12:00:00am time to the dates.

I recommend the former unless times are critical to your analysis. DateTime fields in Power BI consume way more space due to their cardinality.

[jtknowledge]

That worked perfectly! Thank you so much!!

[edhans]

Glad to be of assistance @jtknowledge