Forum Discussion
How to get the most recent value in a date range based on two criteria?
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
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.
I've started working on a DAX solution but I don't have the time I'd need to finish it.
In case it is useful to you, this is where I got to but it's not working correctly based on the max date.
MaxDebt = VAR MinDate = CALCULATE ( MIN ( 'Table'[DebtDate] ), ALLSELECTED ( 'Date'[Column1] ) ) VAR MaxDate = CALCULATE ( MAX ( 'Table'[DebtDate] ), ALLSELECTED ( 'Date'[Column1] ) ) VAR MaxDebt = CALCULATE ( MAX ( 'Table'[DebtOwing] ), FILTER ( 'Date', 'Date'[Column1] >= MinDate && 'Date'[Column1] <= MaxDate ) ) VAR TotalMaxDebt = SUMX ( SUMMARIZE ( 'Table', 'Table'[ID], "MV", MAX ( 'Table'[DebtOwing] ) ), [MV] ) RETURN IF ( HASONEVALUE ( 'Table'[ID] ), MaxDebt, TotalMaxDebt )There's a chance I'm over complicating this.
Someone with stronger DAX Fu skills will probably get you an answer pretty quickly.
(these guys spring to mind)
AlexisOlson, PaulDBrown, mahoneypat, TomMartens - can someone show wowthisistricky (and me) how this should be done.