PowerBi tiled date slicer to cover previous values

Question

I need a slicer, ideally buttons to select year and month, that references all previous values in a table to look up the current price.

I've tried the tiled option slicer linked to my date table but when selecting the required month it doesn't see or reference the previous months data, only if the particular item arrived that month. For some months it only seems to see an item if the date is specifically selected, for others it sees them whenever they arrived during the month.

I'd like to be able to see the last price paid for an item even if it arrived months previously.

Is there something wrong with the date table? Do I need to set up date hierarchy?

Answer 1

If your FactTable is ordered by date then:

MyMeasure = 
  CALCULATE(
    LASTNONBLANK('FactTable'[Amount], 1),
    ALL('DateTable'), 'FactTable'[Date] <= MAX('DateTable'[Date])
  )

If not then:

MyMeasure = 
  MAXX(
    TOPN(1, 
      CALCULATETABLE(
        'FactTable', 
        ALL('DateTable'),
        'FactTable'[Date] <= MAX('DateTable'[Date]) && NOT ISBLANK('FactTable'[Amount])
      ),
      'FactTable'[Date], DESC
    ),
    'FactTable'[Amount]
  )