Give back Latest value based on valid from/to

[dev_dk]

Hi all

I have a challenge, but sadly i got stuck. So i want to use only one date slicer, for filtering table values which is look look like something this:

ID	Name	Quantity	Valid_From	Valid_To
1	Anna	10	2022-12-30	2023-01-15
2	Anna	15	2023-01-15	2023-03-03
3	Robert	22	2022-10-07	2022-11-11
4	Anna	11	2023-03-03	2023-03-23
5	Robert	42	2022-11-11	2023-02-02
6	John	32	2023-01-02	2023-03-11

The problem i want solve is if I use between slicer and choose a First Date and Last Date I want give back the Latest value.

I know i can use somthing like "selectedvalue(Table[Valid_From])>=FIRSDATE('Calendar'[Date]) && selectedvalue(Table[Valid_From])<=LASTDATE('Calendar'[Date])" but i need to use this in visuals that dont use this date values for example a card visual with this measure SUM(Quantity)

For an example i choose 2022-12-30 and 2023-03-02 the answer will be:

Anna	15
Robert	42
John	32

So i like to see in my car visual 89 (where i use sum(Quantity)) .

Thanks in advance!

[johnt75 · Accepted solution]

Make sure that your date table isn't connected to the fact table, and mark the ID column of the fact table as the key column, in the model view. Then you can create a measure like

Selected Quantity =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR MinDate =
    MIN ( 'Date'[Date] )
RETURN
    SUMX (
        INDEX (
            1,
            FILTER (
                'Table',
                'Table'[Valid_From] <= MaxDate
                    && 'Table'[Valid_To] >= MinDate
            ),
            ORDERBY ( 'Table'[Valid_To], DESC ),
            PARTITIONBY ( 'Table'[Name] )
        ),
        'Table'[Quantity]
    )