Maximum value vs selected date power bi

Question

I have the following table where I present inventory information of some materials (for example, dummy data of a single material).

Werk   material   description   stock      $$       date 
 us2     1          material1     4      154.15     202307
 us3     1          material1     3       67.08     202310
 us5     1          material1     1        2.00     202308
 us2     1          material1    10      198.00     202311
 us3     1          material1     0        0.00     202311

Since it is inventory information, I need to obtain the last value of each material by werk. That is, in the month of November, obtain the following data

Werk   material   description   stock      $$       date 
 us5     1          material1     1        2.00     202308
 us2     1          material1    10      198.00     202311
 us3     1          material1     0        0.00     202311

The problem is that when I want to put a filter with the "date" field, if I select, for example, "202311" it will only show me the data for werks 2 and 3

How can I put a filter and when I select a date it gives me the last value before that selected date

Answer 1

It is not possible to show additional rows from a table that has been directly filtered/sliced.

So to achieve what you are after, you will need a separate table that will be used for your filter/slicer. Create a new Calculated Table with:

Inventory Date = DISTINCT('inventory'[date])

Ensure no relationship exists between Inventory Date table and your inventory table. Then create a measure similar to this one:

Filtered Count = 
  var minD = MIN('Inventory Date'[date])
  var thisM = SELECTEDVALUE(inventory[material])

  var prevRow = 
    TOPN(1, 
      FILTER(ALL(inventory), inventory[material] = thisM && inventory[date] < minD),
      [Werk], DESC
    )
  var dateRows =
    FILTER(
      ALLSELECTED(inventory),
      inventory[date] IN DISTINCT('Inventory Date'[date])
    )
  var allRows = UNION(dateRows, prevRow)

  return COUNTROWS( INTERSECT(allRows, inventory) )

Now add this measure as a Visual Filter in the Filter pane, and set it to is not blank.

Note
The above will include the one row per material with the max Werk before the selected date. It wasn't clear if you needed to show all unique Werks rows for the date selected prior. For example, if Nov only had Werk = us2, what rows should it show?