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: ID Name Quantity Valid_From Vali...
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]
    )
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]
    )