[SOLVED] Dax measure to look up the latest value in a column based on a date slicer

Dax measure to look up the latest value in a column based on a date slicer

I have a table that has three columns, date (going back to 2017), product (same group of stock items ordered at different times) and price (price varies each time ordered).

I calculate the total stock issued each month and need a measure that looks up the last price, not the max price, based on a date selected by a slicer. The final aim being to have a report that I can quickly analyse year of stock data.

The measure I have MAXX('stockin','stockin'[unitprice]) sort of works but only gives the maximum price paid or if the item wasn't ordered that month(as selected on the slicer), no value (stock can be issued but not ordered for the month).

I tried to make some of the similar solutions work but can't seem to make them work for me.

Can anyone offer any solutions or ideas for this?

Solution

If your table is in the right order, then you can use either FIRSTNONBLANK or LASTNONBLANK.

LASTNONBLANK('stockin'[unitprice], 1)

And if your are looking for the last value even before the slicer range then:

YourMeasure = 
  var maxDateSelected = LASTDATE('YourDateTable'[Date])
  var maxDateAvaialble = CALCULATE(
    LASTDATE('stockin'[date]),
    FILTER(ALL('stockin'), NOT ISBLANK('stockin'[unitprice]) && 'stockin'[date] <= maxDateSelected )
  )

  RETURN CALCULATE(
      LASTNONBLANK('stockin'[unitprice], 1),
      FILTER( ALL('stockin'[date]), 'stockin'[date] = maxDateAvaialble )
    )