DAX Calculation - Find value based on day (relative)

[edgarMunoz]

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, which are based on updates happening in the source system. Althought there seems to be a trend on teh dates when the updates occur, the reality is the updates don't necessarilly occur in the same date for all the accounts (as an example, for the period covered in the information below, Account_1 and Account_2 had 12 updates). 

I need a measure to calculate the total balance for a specific date (usually month end). The challenges are:

1. There is not always a record for that specific date. For example, if the reference date is 30/11/2025, I will not find a specific record with that date for each account. So, I need to find the closest one. This would be something like a VLOOKUP in Excel using TRUE as a range_lookup parameter (find the closest one).

2. The measure must evaluate by account. If the reference date is 5/09/2025, the I need to find the record closest to the reference day by account (evaluting in ascending order)

As an example, if the reference day is 5/09/2025, the measure should output: $53,972.78:

- Account_1: Value from 4/09/2025 --> $22,197.28

- Account_2: Value from 4/09/2025 --> $29,275.50

- Account_3: Value from 2/09/2025 --> $2,500.00 (notice here there is an update on 6/09/2025, but the closest date when evaluting in ascending order is 2/09/2052)

RecordDate	AccountNumber	Balance
1/07/2025	Account_1	0
5/08/2025	Account_1	11178.03
2/09/2025	Account_1	22197.28
4/09/2025	Account_1	22197.28
12/09/2025	Account_1	0
1/10/2025	Account_1	10173.87
31/10/2025	Account_1	10173.87
3/11/2025	Account_1	21186.58
5/11/2025	Account_1	11012.71
10/11/2025	Account_1	0
1/12/2025	Account_1	10850.22
23/12/2025	Account_1	0
1/07/2025	Account_2	0
5/08/2025	Account_2	14880.22
2/09/2025	Account_2	29275.5
4/09/2025	Account_2	29275.5
12/09/2025	Account_2	0
1/10/2025	Account_2	14295.86
31/10/2025	Account_2	14295.86
3/11/2025	Account_2	29243.84
5/11/2025	Account_2	14947.98
10/11/2025	Account_2	0
1/12/2025	Account_2	14541.29
23/12/2025	Account_2	0
1/07/2025	Account_3	0
3/07/2025	Account_3	0
4/07/2025	Account_3	0
5/08/2025	Account_3	2978.63
2/09/2025	Account_3	2500
6/09/2025	Account_3	5504
12/09/2025	Account_3	0
1/10/2025	Account_3	2809.55
31/10/2025	Account_3	2809.55
3/11/2025	Account_3	5356.78
5/11/2025	Account_3	2547.23
10/11/2025	Account_3	0
1/12/2025	Account_3	2500.69
23/12/2025	Account_3	0

[Olufemi7]

Hello @edgarMunoz, 

You need a measure that finds the latest balance on or before a reference date per account, then sums across accounts. The correct DAX pattern uses `CALCULATE` with `MAX` to identify the latest date ≤ reference date, and then retrieves the balance.

 Step-by-Step DAX Solution

1. Balance as of Reference Date (per account)

BalanceAsOfDate =
VAR RefDate = SELECTEDVALUE ( 'Calendar'[Date] )
VAR LatestDate =
CALCULATE (
MAX ( Data[RecordDate] ),
FILTER ( Data, Data[RecordDate] <= RefDate )
)
RETURN
CALCULATE (
MAX ( Data[Balance] ),
FILTER ( Data, Data[RecordDate] = LatestDate )
)

• `SELECTEDVALUE('Calendar'[Date])` → reference date from your Date table.
• Finds the latest record date ≤ RefDate for the current account.
• Returns the balance at that date.

2. Total Across Accounts

TotalBalanceAsOfDate =
SUMX (
VALUES ( Data[AccountNumber] ),
[BalanceAsOfDate]
)

• Iterates over each account.
• Uses the measure above to get the balance as of the reference date.
• Sums them up.

 Example (Reference Date = 5/09/2025)

• Account_1 → 4/09/2025 → $22,197.28
• Account_2 → 4/09/2025 → $29,275.50
• Account_3 → 2/09/2025 → $2,500.00
Total = $53,972.78

Relevant Microsoft Documentation

FILTER function (DAX) - DAX | Microsoft Learn https://learn.microsoft.com/en-us/dax/filter-function-dax

CALCULATE function (DAX) - DAX | Microsoft Learn https://learn.microsoft.com/en-us/dax/calculate-function-dax

LASTNONBLANKVALUE function (DAX) - DAX | Microsoft Learn https://learn.microsoft.com/en-us/dax/lastnonblankvalue-function-dax

Use CALCULATE + FILTER + MAX to find the latest date ≤ reference date.
• Use SUMX to aggregate across accounts.
• If you want the closest date in either direction (before or after), you’d need a different approach using `MINX` with absolute differences.
• For month-end reporting, tie your reference date to the last day of the month in your Calendar table.

[v-echaithra]

Hi @edgarMunoz ,

Thank you @FreemanZ , @maruthisp , @ThxAlot , @Irwan for your inputs.
I just wanted to check if the issue has been resolved on your end, or if you require any further assistance. Please feel free to let us know, we’re happy to help!

Thank you 
Chaithra E.

[FreemanZ · Accepted solution]

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.

[maruthisp]

Hi @edgarMunoz ,

As per my understanding, tried to derive a solution in the below pbix file.

DAX Calculation - Find value based on day (relative).pbix

Hope this help to solve the problem. Please let me know if you have any further questions or need clarifications.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[ThxAlot]

Easy enough,

[Irwan]

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]

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]

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.