Forum Discussion

wowthisistricky's avatar
wowthisistricky
New Member
4 years ago
Solved

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...
Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    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

     

mahoneypat's avatar
mahoneypat
Microsoft Employee
4 years ago

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's avatar
wowthisistricky
New Member
4 years ago

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!!