Filtering data between validity periods from an input parameter

[Nicolas_H]

Hello,

I need to filter some data with a refence date (which is entered by the user) using 2 dates (start date & end date) that define a validity period.

My table contains :

I want to select a date (idealy by pick in a calendar object) and filter the line where :

[Start Date] < "Selected Date" < [End Date]

for example :

if i pick 20/12/2022, i will filter this line

Also, i have others tables where i want to reproduce this feature, so i would like to use only one date (from my calendar table ?) to drive all the filters on validity periods.

Can someone please help me ?

Thanks.

Nicolas

[DataInsights]

@Nicolas_H,

Create the measure below and use it as a visual filter ("is greater than 0"). The Calendar table does not have a relationship with the fact table. The date slicer uses the Calendar table.

Visual Filter = 
VAR vSelectedDate =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR vResult =
    SUMX (
        FactTable,
        IF ( vSelectedDate > FactTable[Start Date] && vSelectedDate < FactTable[End Date], 1 )
    )
RETURN
    vResult

[Nicolas_H]

Thanks for your answer, it's a great step for me 🙂

So if i understant well, i have to create a measure to filter each table with validity periods ?

And is there a mean to drive all these filters with only one "general filter" ?

Because if i've got 20 tables with validity periods, when i want to cross all the data in one report table, i 'd prefer use 1 filter rather than 20...

Thanks

Nicolas

[DataInsights · Accepted solution]

@Nicolas_H,

You could use SWITCH to determine which table is applicable:

Visual Filter = 
VAR vSelectedDate =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR vResult =
    SWITCH (
        TRUE,
        ISINSCOPE ( FactTable[Start Date] ),
            SUMX (
                FactTable,
                IF (
                    vSelectedDate > FactTable[Start Date]
                        && vSelectedDate < FactTable[End Date],
                    1
                )
            ),
        ISINSCOPE ( FactTable2[Start Date] ),
            SUMX (
                FactTable2,
                IF (
                    vSelectedDate > FactTable2[Start Date]
                        && vSelectedDate < FactTable2[End Date],
                    1
                )
            )
    )
RETURN
    vResult

[Nicolas_H]

I think i can do with that

Thanks for the help