LASTNONBLANKVALUE / LOOKUP - did I make this too complex?

dedelman_clng — Fri, 23 Apr 2021 21:04:41 GMT

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.

Re: LASTNONBLANKVALUE / LOOKUP - did I make this too complex?

Anonymous — Fri, 23 Apr 2021 19:56:40 GMT

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.

Re: LASTNONBLANKVALUE / LOOKUP - did I make this too complex?

dedelman_clng — Fri, 23 Apr 2021 21:03:45 GMT

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

Still curious on the calculation as a whole.

Re: LASTNONBLANKVALUE / LOOKUP - did I make this too complex?

daxer-almighty — Sat, 24 Apr 2021 08:13:05 GMT

// 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

topic LASTNONBLANKVALUE / LOOKUP - did I make this too complex? in DAX Commands and Tips

LASTNONBLANKVALUE / LOOKUP - did I make this too complex?

Re: LASTNONBLANKVALUE / LOOKUP - did I make this too complex?

Re: LASTNONBLANKVALUE / LOOKUP - did I make this too complex?

Re: LASTNONBLANKVALUE / LOOKUP - did I make this too complex?