Power Pivot merge based on min/max values

[mattymc84]

Hi all,

I'm building an aged debtor report using multiple tables. I've used Power Query to format and transform all my tables and have now pulled them all into the data model so I can create relationships in Power Pivot.

My data table is a list of outstanding invoices, with a field called "days due", which is either the number of days until or past the due date.

I have a mapping table for ageing brackets, with the fields "min", "max" and ageing bracket e.g.
Min Max Ageing Bracket
31 60 31-60

How do a find join/lookup the "days due" field against the mapping table, so it determines which ageing bracket to allocate an invoice?

Any help would be welcome, I'm a DAX virgin.

Thanks

Matt

 

[gazpage]

Power Pivot relationships deal with exact matches, not with ranges. There are ways around it, but the simplest thing (assuming that your days overdue are integers), would be to create a complete look up table, e.g.

Days Bracket
1 1-30
2 1-30
3 1-30
...
31 31-60

Then link the days column to the column in your main table and you will be able to filter by the bracket, which I assume is your goal.

 

[mattymc84]

Power Pivot relationships deal with exact matches, not with ranges. There are ways around it, but the simplest thing (assuming that your days overdue are integers), would be to create a complete look up table, e.g.

Days Bracket
1 1-30
2 1-30
3 1-30
...
31 31-60

Then link the days column to the column in your main table and you will be able to filter by the bracket, which I assume is your goal.

Thanks Gaz, I appreciate the response. This is something that I had already considered, but I guess I'll have to run with it.

Thanks again,

Matt