Forum Discussion
How to get the most recent value in a date range based on two criteria?
I have a single table and in which each row represents the debt that is owing for an account on the noted date.
Over a time period the amount of debt owing for each account can vary, when the debt amount owing changes for an account a new row appears in this table and is date stamped reflecting when this event occurs.
For arguments sake there are only four accounts and a sample dataset is as follows – practically however there are 1000’s of accounts:
| ID | DebtDate | DebtOwing |
| 111 | 1-Oct-21 | $10 |
| 111 | 2-Oct-21 | $20 |
| 111 | 4-Oct-21 | $25 |
| 111 | 7-Oct-21 | $30 |
| 222 | 3-Oct-21 | $600 |
| 222 | 9-Oct-21 | $700 |
| 333 | 30-Sept-21 | $1000 |
| 333 | 29-Oct-21 | $900 |
| 444 | 11-Oct-21 | $100 |
| 444 | 15-Oct-21 | $90 |
On a given calendar date if no account’s debt changes then there are no row entries in the table for that date. If however the debt owing for an account does changes then there is a row entry for each account for which the debt has changed – if an accounts debt changes multiple times over the course of a single day only one row entry appears for that day for that account and reflects the debt owing after the last change.
I would like to be able to create a measure for the above table and which returns the debt owing that corresponds to the ‘max’ debt date for a configurable date rate e.g. if the date range selected is [1-Oct-21] to [10-Oct-21] then I can use this measure to create a table in my report UI as follows:
| ID | Most Recent Debt Date | Most Recent Debt Owing |
| 111 | 7-Oct-21 | $30 |
| 222 | 9-Oct-21 | $700 |
I would also like to be able to create a PowerBI Card in my report UI that would use the same measure (?) or instead via a separate measure it would return the value $730 – this being the sum of $90 plus $300 in the above example.
Unfortunately I am struggling to get my head around DAX/Power query and have gone a little nuts trying to solve the above, accordingly any help and guidance that can be offered will be really (really) appreciated, thanks!
Here is one way to do it. Put your ID and DebtDate columns in a table visual and don't summarize either, along with this measure. Result is shown below (note your sample data didn't have the 9/9 row).
Latest Debt =
VAR thisdebtdate =
MAX ( Debt[DebtDate] )
VAR maxseldate =
CALCULATE ( MAX ( Debt[DebtDate] ), ALLSELECTED ( Debt[DebtDate] ) )
VAR result =
CALCULATE ( MAX ( Debt[DebtOwing] ), Debt[DebtDate] = maxseldate )
RETURN
IF ( thisdebtdate = maxseldate, result )Pat
7 Replies
Attached is a Power Query solution which should get you most of the way there.
Let me know if you have questions.
KNP, thank you for taking the time to reply 🙂
In doing so however it doesn’t quite solve the conundrum I am trying to solve, sorry.
To explain, I have expanded the data table to now and in addition include for-example data for September:ID DebtDate DebtOwing 111 1-Oct-21 $10 111 2-Oct-21 $20 111 4-Oct-21 $25 111 7-Oct-21 $30 222 3-Oct-21 $600 222 9-Oct-21 $700 333 30-Sep-21 $1000 333 29-Oct-21 $900 444 11-Oct-21 $100 444 15-Oct-21 $90 111 1-Set-21 $110 111 3-Set-21 $111 222 2-Set-21 $660 222 7-Sep-21 $560 333 4-Set-21 $9900 444 17-Sep-21 $330
If given the above the start/end date is changed to [1-Sept-21] to [10-Sept-21] then the data rows of interest are the highlighted-in-green rows below – for the original date range of [1-Oct-21] to [10-Oct-21] then the highlighted-in-yellow rows are of interest:
What I don’t know is advance is what the date range will be that the end user sets to filter the data hence then I cant statically persist the max value for an ID in a table, it needs to be calculated dynamically.
Were I to solve this in SQL I would write a Query #1 below to return a table dataset and Query #2 to give the overall value, accordingly I think that I need a measure that can power-query this on the fly alas though I cant for the life of me figure out how to do this, tears!No, you're right, that was never going to work, sorry.
I'll see if I can figure out the DAX.
KNP, thank you for taking the time to reply 🙂
In doing so however it doesn’t quite solve the conundrum I am trying to solve, sorry.
To explain, I have expanded the data table to now and in addition include for-example data for September:ID DebtDate DebtOwing 111 1-Oct-21 $10 111 2-Oct-21 $20 111 4-Oct-21 $25 111 7-Oct-21 $30 222 3-Oct-21 $600 222 9-Oct-21 $700 333 30-Sep-21 $1000 333 29-Oct-21 $900 444 11-Oct-21 $100 444 15-Oct-21 $90 111 1-Set-21 $110 111 3-Set-21 $111 222 2-Set-21 $660 222 7-Sep-21 $560 333 4-Set-21 $9900 444 17-Sep-21 $330
If given the above the start/end date is changed to [1-Sept-21] to [10-Sept-21] then the data rows of interest are the highlighted-in-green rows below – for the original date range of [1-Oct-21] to [10-Oct-21] then the highlighted-in-yellow rows are of interest:
What I don’t know is advance is what the date range will be that the end user sets to filter the data hence then I cant statically persist the max value for an ID in a table, it needs to be calculated dynamically.
Were I to solve this in SQL I would write a Query #1 below to return a table dataset and Query #2 to give the overall value, accordingly I think that I need a measure that can power-query this on the fly alas though I cant for the life of me figure out how to do this, tears!Here is one way to do it. Put your ID and DebtDate columns in a table visual and don't summarize either, along with this measure. Result is shown below (note your sample data didn't have the 9/9 row).
Latest Debt =
VAR thisdebtdate =
MAX ( Debt[DebtDate] )
VAR maxseldate =
CALCULATE ( MAX ( Debt[DebtDate] ), ALLSELECTED ( Debt[DebtDate] ) )
VAR result =
CALCULATE ( MAX ( Debt[DebtOwing] ), Debt[DebtDate] = maxseldate )
RETURN
IF ( thisdebtdate = maxseldate, result )Pat
