How to pull a balance date and master data date

Hi bweiland,
Thank you for reaching out to the Microsoft fabric community forum. 

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.    

Dax Measure for Latest Master Data date:

Latest MasterData Date =

VAR SelectedDate = MAX(Balance[BalanceDate])

VAR CurrentAcct = SELECTEDVALUE(Balance[AccountNumber])

RETURN

CALCULATE (

    MAX(MasterData[MasterDate]),

    FILTER (

        MasterData,

        MasterData[AccountNumber] = CurrentAcct &&

        MasterData[MasterDate] <= SelectedDate

    )

)

Dax Measure for Latest Risk Rating:

Latest Risk Rating =

VAR SelectedDate = MAX(Balance[BalanceDate])

VAR CurrentAcct = SELECTEDVALUE(Balance[AccountNumber])

VAR LatestDate =

    CALCULATE (

        MAX(MasterData[MasterDate]),

        FILTER (

            MasterData,

            MasterData[AccountNumber] = CurrentAcct &&

            MasterData[MasterDate] <= SelectedDate

        )

    )

RETURN

CALCULATE (

    MAX(MasterData[RiskRating]),

    FILTER (

        MasterData,

        MasterData[AccountNumber] = CurrentAcct &&

        MasterData[MasterDate] = LatestDate

    )

)

Relationship between both tables:

outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

Hi bweiland , here's a Power Query solution you can try out by pasting it in the Advance Editor:
1. Please make sure that you have two Tables: TableBalance and TableMasterData (This Table is being referred in the TableBalance Data as you'll see in the code). Thanks. Do let me know if you have any queries regarding the same.

Here's the code:

let
TableBalance = #table(
{"Account Number", "Date", "Balance"},
{
{1, #date(2025, 1, 31), 100},
{1, #date(2025, 1, 31), 100},
{1, #date(2025, 3, 31), 200}
}
),

Transform = Table.AddColumn(
TableBalance,
"Record",
each Table.FillDown(
Table.FromColumns(
Table.ToColumns(TableMasterData) & { { _[Date] } }
),
{"Column4"}
)
),

Record = Table.TransformColumns(
Transform,
{
"Record",
each Table.FirstN(
Table.Sort(
Table.SelectRows(
Table.AddColumn(_, "Check", each [Column4] >= [Column2]),
each [Check] = true
),
{ "Column2", Order.Descending }
),
1
)
}
),

Cols = List.Transform(
List.Skip(Table.ColumnNames(TableBalance)),
each "Master " & _
),

Table = Record,

Expand = Table.ExpandTableColumn(
Table,
"Record",
{"Column2", "Column3"},
Cols
)
in
Expand