Dynamically show the latest value of Items in a selected date range in another slicer.

[Anonymous]

Hi, I need a help with one DAX Scenario. I have an item table in which the item names can be changing without changing its id. I need to show an item slicer with the latest value of the items only according to the slicer values in the date.

Scenario :

My Item name is changed from A to B on 5 Jan 2020, B to C on 15 Jan 2020 and C to D on 18th Jan 2020. ItemId remains the same for this item and let's assume it's 1.

An another item, itemid 2 is also there. This items name from X is changed to Y on 3 Jan 2020 and then to Z on 14 Jan 2020.

Now on my relative date slicer if I select 1 Jan 2020 to 20 Jan 2020, we need to represent only D & Z in an another slicer that is Item slicer. This is because these are the latest values according to the date selection.

If I select 10 Jan - 15 Jan, then the values in slicer would be C & Z.

Hope I explained the scenario correctly and could someone help with this please.

Regards,

Jishnu

 

[sturlaws]

Hi @Anonymous 

you will need to have columns for validFrom and validTo. The change of date will be the validTo column, while the validFrom column is computed like this:

ValidTo =
VAR _item = 'Table'[itemID]
VAR _validTo = 'Table'[ValidTo]
VAR _validFrom =
    CALCULATE (
        MIN ( 'Table'[ValidTo] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[itemID] = _item
                && 'Table'[ValidTo] < _validTo
        )
    )
RETURN
    IF ( ISBLANK ( _validFrom ), DATE ( 1900, 1, 1 ), _validFrom - 1 )

Next create a measure like this:

Measure Filter =
VAR _selectedDate =
    MAX ( Dates[Date] )
RETURN
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[ValidFrom] <= _selectedDate
                && 'Table'[ValidTo] >= _selectedDate
        )
    )

Create a slicer with the item names, and add this measure to the filter of that slicer like this:

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[nandic]

Hi @Anonymous ,
Here is example of calculated measure which returns last name in my demo:

Measure =

var maxDate = MAXX(ALLSELECTED(Sheet1[Date]),Sheet1[Date])

RETURN

CALCULATE(LASTNONBLANK(Sheet1[Item Name],Sheet1[Item Name]),Sheet1[Date]<=maxDate)

It returns last "Name" in selected period of time. As measure can't be used in slicer, we can do a little hack, we will create a list by item id and add this measure. For each item id it will return last item name.
Now as we can't hide item id we can just insert shape and set fill color to white or change transparency. 
In screenshot below i set transparency and border to this inserted shape - in real life this shape will hide item id.

Result:


Not the most beautiful solution, but it might help.

Cheers,
Nemanja