Finding previous date

[Anonymous]

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

PreviousDate = CALCULATE(

    MAXX(FBN_Quotation,FBN_Quotation[Date]),

        FILTER(FBN_Quotation,FBN_Quotation[Comp Item Number] = FBN_Quotation[Comp Item Number] && FBN_Quotation[Date] < EARLIER(FBN_Quotation[Date]) && FBN_Quotation[Unit Price] > 0 ))

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

[Anonymous]

@Greg_Deckler 

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 ?

[Greg_Deckler]

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

[Greg_Deckler]

@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

[Anonymous]

@Greg_Deckler 
Thank you so much. It works.
Just a curiosity, what was wrong with my original formula.

[Greg_Deckler]

@Anonymous Without digging into it, probably the use of CALCULATE in a single table data model. That tends to not pan out very well.