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 selected ones. I explain. Let's say I have sales dates in a table ranging from 1 Jan 21 to 25 June 21. ( All products)The slicer shows these dates by default.  Now I apply a filter say on Product ( bicycles) -

 

In  the table the dates can be 10 Feb 21 to 18 March 21 ( these are the dates between,  I haves sales of bicycles) and will be displayed if I choose in  the Date Slicer Dates Date From 9 Feb 21 to 30 March 21. I am looking at how to get the Slicer dates  ( 9 Feb 21 and 30 March 21) and not the Min and Max Dates returned ( 10 Feb 21 and 18 March 21)

 

Thanks to help me.

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])
)

     

7 Replies

  • 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])