Forum Discussion

Suryateza's avatar
Suryateza
Frequent Visitor
3 years ago
Solved

Slicer date range issue

Hi,

 

How can I show the dates before the slicer date selection? The Slicer date range is 7/1/2023-7/20/2023.

I want to show the arrival dates before 7/1, 

For example,

Customer A arrived on  6/29/2023 and departed on 7/06/2023. I want to show this record as his departure is 7/15, which still falls in our date range(7/1/2023-7/20/2023).

 

Can anybody help me with this please?

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Suryateza ,

    Please try below steps:

    1. below is my test table
    Table:

    Date:

     

    Date = CALENDAR(DATEVALUE("2023/07/01"),DATEVALUE("2023/07/20"))

     

    2. create a measure with below dax formula

     

    Measure =
VAR min_date =
    MIN ( 'Date'[Date] )
VAR max_date =
    MAX ( 'Date'[Date] )
VAR cur_arr =
    SELECTEDVALUE ( 'Table'[Arrived] )
VAR cur_dep =
    SELECTEDVALUE ( 'Table'[Departed] )
VAR result =
    SWITCH (
        TRUE (),
        cur_arr >= min_date
            && cur_dep <= max_date, 1,
        cur_arr < min_date
            && cur_dep >= min_date
            && cur_dep <= max_date, 1
    )
RETURN
    result

     

     

    3. add a table visual with Table fields, add a slicer visual with Date table, add measure to table visual filter pane and apply

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Suryateza ,

    Please try below steps:

    1. below is my test table
    Table:

    Date:

     

    Date = CALENDAR(DATEVALUE("2023/07/01"),DATEVALUE("2023/07/20"))

     

    2. create a measure with below dax formula

     

    Measure =
VAR min_date =
    MIN ( 'Date'[Date] )
VAR max_date =
    MAX ( 'Date'[Date] )
VAR cur_arr =
    SELECTEDVALUE ( 'Table'[Arrived] )
VAR cur_dep =
    SELECTEDVALUE ( 'Table'[Departed] )
VAR result =
    SWITCH (
        TRUE (),
        cur_arr >= min_date
            && cur_dep <= max_date, 1,
        cur_arr < min_date
            && cur_dep >= min_date
            && cur_dep <= max_date, 1
    )
RETURN
    result

     

     

    3. add a table visual with Table fields, add a slicer visual with Date table, add measure to table visual filter pane and apply

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • SamInogic's avatar
    SamInogic
    Super User
    3 years ago

    Hi,


    You can Try a different approach creating a new Table using the below DAX Expression,

     

    DateRange = CALENDAR(MINA(Sampletable2[Arrived On]),MAXA(Sampletable2[Departed ON]))

     

    You have to create the relationship between the date range table and your required table.

     

    Please refer to the Below Screenshot,

     

    Thanks!
    Inogic Professional Services Division
    Power Platform and Microsoft Dynamics 365 CRM Development – All under one roof!
    Drop an email at [email protected]
    Services: http://www.inogic.com/services/
    Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
    View solution in original post