How to sum a column based on another column being between a min/max of another table

[dalek1]

My Google Fu is failing me here and maybe I'm just way over complicating something.....

I'm trying to financial reports using a template pulled from another table...

In the model I have our ledger data, has account number, and amount and other stuffs. I've got that in a relationship with a date table so a slicer can pick the date range and filter that table.....

But I have a template table that has a report number, then line indexes, a description, and then a fromacct and toacct column....

So I want to put a table visualization that uses that template tables line numbers, and descrions and then sums the amount from

the ledger table for all the accounts between the fromacct and toacct.

Something like this....

= CALCULATE( SUM(gldet[glamt]), FILTER(gldet, gldet[glacct] >= FinRptFormats[pnl_fmacct] & gldet[glacct] <= FinRptFormats[pnl_toacct]))

.... Like if I was writing this in SQL it would be something along the lines of

SELECT SUM(glamt) FROM gldet WHERE glacct BETWEEN (SELECT toacct FROM FinRptFormats WHERE pnl_report = 100 and pnl_linenum = 10) and (SELECT fromacct FROM FinRptFormats WHERE pnl_report = 100 and pnl_linenum = 10)

Is there a way to construct like a dynamic query/table that could pull that in?

I'm just trying to figure out the logic to keep this dynamic so I can use the formats that already exist in our ERP so if they change them for the reporting there, the dashboard one would generate from that same format.... Rather than me making some sort of static one for each financial report.

I just don't know enough power bi vocabulary to google correctly to find these little gems...

[DataInsights · Accepted solution]

@dalek1,

One approach would be to use the List function in Power Query. Here's an example:

List.Numbers([fromacct], [toacct] - [fromacct] + 1)

This will generate a list of each number in the From/To account range for a particular line in the report template. After expanding the list, you can remove nonexistent accounts via an inner join to the Chart of Accounts table (master list of accounts). The resulting table can be used as your dimension table (e.g., matrix rows), and will group accounts per the report template.

[dalek1]

That looks like it's getting me in the neighborhood of what I needed. I still struggle a bit with getting those lists to work, but it's working here. Thank you.