Forum Discussion

dev_dk's avatar
dev_dk
Frequent Visitor
3 years ago
Solved

Give back Latest value based on valid from/to

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:

IDNameQuantityValid_FromValid_To
1Anna102022-12-302023-01-15
2Anna152023-01-152023-03-03
3Robert222022-10-072022-11-11
4Anna112023-03-032023-03-23
5Robert422022-11-112023-02-02
6John322023-01-022023-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:

Anna15
Robert42
John32

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

Thanks in advance!

 

Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    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]
    )

1 Reply

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    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]
    )