DAX Calculation - Find value based on day (relative)

Easy enough,

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

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]
)

SUM Account = [Account_1]+[Account_2]+[Account_3]

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.

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.