Forum Discussion

Nicolas_H's avatar
Nicolas_H
Frequent Visitor
3 years ago
Solved

Filtering data between validity periods from an input parameter

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

 

Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    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

4 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    3 years ago

    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's avatar
      Nicolas_H
      Frequent Visitor
      3 years ago

      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's avatar
        DataInsights
        Super User
        3 years ago

        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