Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
// 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
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.
You are correct. I was thinking row context would pass across, but it does not.
Still curious on the calculation as a whole.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
27 | |
23 | |
15 | |
11 |
User | Count |
---|---|
76 | |
58 | |
46 | |
17 | |
12 |