Forum Discussion

edgarMunoz's avatar
edgarMunoz
Frequent Visitor
8 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
    8 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.

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

hello edgarMunoz 

 

i might be misunderstood but i assumed the closest date will always be max date before the date selection.

 

1. create a calendar date because as you mentioned above, not all date available.

2. no need to create a relationship between calendar and fact tabl.

3. create 3 measure for each account because every account will have different closest date.

Account_1 =
var _Date = 
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[RecordDate]<=SELECTEDVALUE('Calendar'[Date])&&
        'Table'[AccountNumber]="Account_1"
    ),
    'Table'[RecordDate]
)
Return
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[RecordDate]=_Date&&
        'Table'[AccountNumber]="Account_1"
    ),
    'Table'[Balance]
)
ccount_2 =
var _Date = 
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[RecordDate]<=SELECTEDVALUE('Calendar'[Date])&&
        'Table'[AccountNumber]="Account_2"
    ),
    'Table'[RecordDate]
)
Return
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[RecordDate]=_Date&&
        'Table'[AccountNumber]="Account_2"
    ),
    'Table'[Balance]
)
ccount_3 =
var _Date = 
MAXX(
   FILTER(
        ALL('Table'),
        'Table'[RecordDate]<=SELECTEDVALUE('Calendar'[Date])&&
        'Table'[AccountNumber]="Account_3"
    ),
    'Table'[RecordDate]
)
Return
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[RecordDate]=_Date&&
        'Table'[AccountNumber]="Account_3"
    ),
    'Table'[Balance]
)
4. create a measure for sum all three measures.
SUM Account = [Account_1]+[Account_2]+[Account_3]
 
Hope this will help.
Thank you.
 
edgarMunoz's avatar
edgarMunoz
Frequent Visitor
8 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
    8 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.