Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Finding previous date

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

 

 

Need Help

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

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

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Greg_Deckler
      Community Champion
      3 years ago

      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