LASTNONBLANKVALUE / LOOKUP - did I make this too complex?

[dedelman_clng]

Model: 

- Calendar table 'Date' with DateSK (YYYYMMDD) and Date (MM/DD/YYYY) columns

- Material_Metrics_Mth: weekly snapshot of inventory values. Relationship on 'Date' to 'Date'[DateSK]. Also contains column [Dt] as MM/DD/YYYY. 

- ZMM_Material_Movements_DM: Transaction table for issues, receipts, etc. Has only [PostingDate] as relationship to 'Date'[Date].

Sample Data

Material Metrics Mth

MaterialNum	UnitCost	Date	Dt
307460	3143.56	20210418	4/18/2021
307460	3143.56	20210411	4/11/2021
307460	3143.56	20210404	4/4/2021
307460	3143.56	20210331	3/31/2021
307460	3143.56	20210328	3/28/2021
307460	3143.56	20210321	3/21/2021
307460	3143.56	20210314	3/14/2021
307460	3143.56	20210307	3/7/2021
307460	3143.56	20210228	2/28/2021
307460	3143.56	20210221	2/21/2021
307460	3143.56	20210214	2/14/2021
307460	3143.56	20210207	2/7/2021
307460	3143.56	20210131	1/31/2021
307460	3143.56	20210124	1/24/2021
307460	3143.56	20210117	1/17/2021
307460	3143.56	20201231	12/31/2020
307460	3143.56	20201130	11/30/2020
307460	3000.54	20201031	10/31/2020
307460	3000.54	20200930	9/30/2020
307460	3000.54	20200831	8/31/2020
307460	3000.54	20200731	7/31/2020
307460	3000.54	20200630	6/30/2020

ZMM_Material_Movements

MaterialDocItem	MaterialDocument	MaterialNum	MovementType	PostingDate (MM/DD/YYYY)	Inv Value at Trx
1	4900044106	307460	261	1/12/2021	3143.56
1	4900044108	307460	261	1/12/2021	3143.56
4	5000026616	307460	101	11/2/2020	3000.54
3	4900043033	307460	201	10/3/2020	3000.54

Bolded column is calculate and the subject of this post.

Basically, the requirement is to find the last UnitCost for the given MaterialNum on or before Posting Date. I am getting the right answer with the following code, but I have this suspicion I made it more complex than it needed to be. Please review the code and see if it can be simplified at all. Mostly I couldn't get from a LASTNONBLANK output (table with single row/column) to a scalar that I could use in LOOKUPVALUE.

Calculated Column [Inv Value at Trx]

Inv Value at Trx = 
VAR __TrxDate = ZMM_MATERIAL_MOVEMENTS_DM[PostingDate] //This column's Posting Date

VAR __LNB =  //YYYYMMDD value for the latest date at or before TrxDate 
    LASTNONBLANKVALUE (
        SUMMARIZE (
            FILTER (
                Material_Metrics_Mth,
                Material_Metrics_Mth[Dt] <= __TrxDate
            ),
            [Dt]
        ),
        MAX ( Material_Metrics_Mth[Date] )
    )

VAR __LNBD =  //"Convert" back to MM/DD/YYYY
    LOOKUPVALUE ( 'Date'[Date], 'Date'[DateSK], __LNB )

RETURN  //Finally, get the specific value we're looking for
    LOOKUPVALUE (
        Material_Metrics_Mth[UnitCost],
        Material_Metrics_Mth[MaterialNum], ZMM_MATERIAL_MOVEMENTS_DM[MaterialNum],
        Material_Metrics_Mth[Dt], __LNBD
    )

Thanks for any assistance,

David

EDIT: Removed an ALLEXCEPT call that was extraneous.

[daxer-almighty]

// This measure works the way you want on condition
// that there can't be 2 different unit costs on 
// the same date for the same material in the
// Material_Metrics_Mth. But I guess this is true.

[Inv Value at Trx] =
var __TrxDate = ZMM_MATERIAL_MOVEMENTS_DM[PostingDate]
var __MaterialNum = ZMM_MATERIAL_MOVEMENTS_DM[MaterialNum]
var __ValueAtTrx =
    MAXX(
        topn(1,
            filter(
                Material_Metrics_Mth,
                AND(
                    Material_Metrics_Mth[Dt] <= __TrxDate,
                    Material_Metrics_Mth[MaterialNum] = __MaterialNum
                )
            )
            Material_Metrics_Mth[Dt],
            DESC
        ),
        Material_Metrics_Mth[UnitCost]
    )
RETURN
    __ValueAtTrx

[Anonymous]

One thing I can tell you right off the bat is this: your ALLEXCEPT does nothing at all since 1) in a calculated column you don't have any filter context and 2) there is no CALCULATE that would create one.

[dedelman_clng]

You are correct. I was thinking row context would pass across, but it does not.

Still curious on the calculation as a whole.