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.

edgarMunoz's avatar
edgarMunoz
Frequent Visitor
9 months ago

Hi Irwan,
Thanks for your help. This is the right track, however, I have thousands of accounts, so creating a calculated by account is not feasible.

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

hello edgarMunoz 

 

here is the adjustment.

 

1. create a measure for each account balance (if you dont need this value, the you can skip this measure).

Account =
var _Date = 
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[RecordDate]<=SELECTEDVALUE('Calendar'[Date])&&
        'Table'[AccountNumber]=SELECTEDVALUE('Table'[AccountNumber])
    ),
    'Table'[RecordDate]
)
Return
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[RecordDate]=_Date&&
        'Table'[AccountNumber]=SELECTEDVALUE('Table'[AccountNumber])
    ),
    'Table'[Balance]
)
2. create a measure for sum total of all account in closest date from date selection.
SUM Total =
var _Date = 
SUMMARIZE(
    ADDCOLUMNS(
        ALL('Table'),
        "Closest Date",
        MAXX(
            FILTER(
                ALL('Table'),
                'Table'[RecordDate]<=SELECTEDVALUE('Calendar'[Date])&&
                'Table'[AccountNumber]=EARLIER('Table'[AccountNumber])
            ),
            'Table'[RecordDate]
        )
    ),
    'Table'[AccountNumber],
    [Closest Date],
    "Balance",
    MAXX(
        FILTER(
            ALL('Table'),
            'Table'[RecordDate]=[Closest Date]&&
            'Table'[AccountNumber]=EARLIER('Table'[AccountNumber])
        ),
        'Table'[Balance]
    )
)
Return
SUMX(
    _Date,
    [Balance]
)
 
Hope this will help.
Thank you.