Forum Discussion

edgarMunoz's avatar
edgarMunoz
Frequent Visitor
9 months ago
Solved

DAX Calculation - Find value based on day (relative)

Hi community, I'm seeking some help to solve a challenge I'm facing to create a measure.    I have a table with the structure below. Each account (AccountNumber column) has multiple records, whi...
  • FreemanZ's avatar
    FreemanZ
    9 months ago

    hi edgarMunoz ,

     

    Not sure if i fully get you, supposing you have a reference date slicer fed with a disconnected dates table, you may try to write the measure like this:

    Measure = 
SUMX(
    VALUES(balance[AccountNumber]),
    CALCULATE(
        MAXX(
            TOPN(1, FILTER(balance, balance[RecordDate]<=MAX(dates[Date])), balance[RecordDate]),
            balance[Balance]
        )
    )
)

     

    it works like:

     

    Please find more in the attachment.

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
9 months ago

hi edgarMunoz ,

 

Not sure if i fully get you, supposing you have a reference date slicer fed with a disconnected dates table, you may try to write the measure like this:

Measure = 
SUMX(
    VALUES(balance[AccountNumber]),
    CALCULATE(
        MAXX(
            TOPN(1, FILTER(balance, balance[RecordDate]<=MAX(dates[Date])), balance[RecordDate]),
            balance[Balance]
        )
    )
)

 

it works like:

 

Please find more in the attachment.