How to get the most recent value in a date range based on two criteria?

[wowthisistricky]

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!

[KNP]

Attached is a Power Query solution which should get you most of the way there.

Let me know if you have questions.

[Syndicate_Admin]

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!

[mahoneypat · Accepted solution]

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

[wowthisistricky]

Pat, wow-ee, so simple, so elegant! Big BIG thank you for your response and the solution. Today is now a Happy Day and me here will be spending time now understanding more the mighty CALCULATE function!! 

[wowthisistricky]

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!

[KNP]

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.

[KNP]

No, you're right, that was never going to work, sorry.

I'll see if I can figure out the DAX.