Forum Discussion

Jaweed's avatar
Jaweed
Icon for Helper III rankHelper III
5 years ago
Solved

Retrieving 2 selected dates in Date Slicer

Hi everybody I have been looking for the solution before coming to you. How do I get the 2 dates selected ina date slicer. The dates to be returned should not be the ones in the table, but the selec...
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Jaweed 

    It cannot be done with a single table. you need to create a date table and create a relationship. The measure to get the slicer date will be as follows. I attached a PBIX file.

    Start Date = 
CALCULATE(
    MIN('Table'[Date]),
    ALLEXCEPT('Table',Dates[Date])
)

---------------------------------------------

End Date = 
CALCULATE(
    MAX('Table'[Date]),
    ALLEXCEPT('Table',Dates[Date])
)

     

Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
5 years ago

Jaweed 

It cannot be done with a single table. you need to create a date table and create a relationship. The measure to get the slicer date will be as follows. I attached a PBIX file.

Start Date = 
CALCULATE(
    MIN('Table'[Date]),
    ALLEXCEPT('Table',Dates[Date])
)

---------------------------------------------

End Date = 
CALCULATE(
    MAX('Table'[Date]),
    ALLEXCEPT('Table',Dates[Date])
)

 

Jaweed's avatar
Jaweed
Icon for Helper III rankHelper III
5 years ago

HiFowmy

After I do test, it  does not give the sekected values.

 

 

 

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    Jaweed 

    You can use as the measure as follows

     

     

    Start Date =  MIN('Dates'[Date])
---------------------------------------------
End Date =   MAX('Dates'[Date])

     

    • Jaweed's avatar
      Jaweed
      Icon for Helper III rankHelper III
      5 years ago

      Hi Fowmy

      Thanks a lot. The Date filtering is on Date Table which we will have to create, may be for dates up to 2024. Is there a way to set the Start and End Date in the slicer as the MIN and Max dates which are in the product file rather than the MIN and Max on the Date file created? I appreciate your valuable advice. Sorry for inconvenience, I am just stuck with his.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Jaweed  I, too, had my date table extended to 2025.  I ended up just tightening it up to the current year ( DATE(YEAR(TODAY()),12,31) ) so my default date range was 1/1/20 through the end of this year.

         

        Unless you have a specific reason to extend your date table beyond this year (maybe you have projected sales), I'd suggest keeping it tight.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Fowmy   I figured out the key to making this work was using the Date Table dates for the slicer vs. using the Created Date from my fact table.  Once I did that, everything worked like you said.  THANK YOU!