Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I try to get the previous date and use it as a lookup to get to previous price.
If the month has no unit cost, I'll skip that month.
DAC fomula
From below picture, the month #7 is correct (previous month = 4) as there is no data for month 6 and month 5 cost = $0.
However, the month 12 is wrong. Somehow it show previous month = 10 and I don't even have month 10 data for this item.
I notice that it might pick up month 10 from other item but in the DAX, I already filter data to the same item
I need to find the unit price from the previous date. Before I had previous date in column so I can use lookupvalue.
Now previous date is a measurement, how can I find the unit price of the previous date ?
This is the column form:
Column =
VAR __Date = [Date]
VAR __Item = [Comp Item Number]
VAR __Table = FILTER(ALL('FBN_Quotation'),[Date] < __Date && [Comp Item Number] = __Item && [Unit Price] > 0)
VAR __Result = MAXX(__Table,[Date])
RETURN
__Result
Or you could create this measure for unit price:
Measure =
VAR __Item = MAX('FBN_Quotation'[Comp Item Number])
VAR __PD = [Previous Date Measure]
VAR __Table = FILTER(ALL('FBN_Quotation'),[Date] = __PD && [Comp Item Number] = __Item)
VAR __Result = MAXX(__Table,[Unit Price])
RETURN
__Result
@Anonymous Try:
Measure =
VAR __Date = MAX('FBN_Quotation'[Date])
VAR __Item = MAX('FBN_Quotation'[Comp Item Number])
VAR __Table = FILTER(ALL('FBN_Quotation'),[Date] < __Date && [Comp Item Number] = __Item && [Unit Price] > 0)
VAR __Result = MAXX(__Table,[Date])
RETURN
__Result
@Greg_Deckler
Thank you so much. It works.
Just a curiosity, what was wrong with my original formula.
@Anonymous Without digging into it, probably the use of CALCULATE in a single table data model. That tends to not pan out very well.