Forum Discussion

Manar's avatar
Manar
Helper II
8 years ago
Solved

Filter data using date range from slicer

Hi ,

I am trying to filter submissions count by a range of dates enterd in slicer  (between)

 

For example if a user enter a range of date the table should show the count of submissions for that range of dates.

 

I created this formula, it works when I choose one date but not a range, any help

 

Current_Submission =

var  startdate= max(DimDate[Date])                        

var  enddate = min(DimDate[Date])                                                                                                                                              Return      

calculate(DISTINCTCOUNT(

 

Submisions[Submission Number]),

   Filter (Submisions,  Submisions[Submission Date]>= startdate      &&   Submisions[Submission Date] <= enddate                                                                )                                                                                                                                                                               )

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago
    Current_Submission =

VAR startdate= MIN(DimDate[Date])                     

VAR enddate  = MAX(DimDate[Date])                                                                                                                       Return      

calculate(DISTINCTCOUNT(Submisions[Submission Number]),
		Filter (Submisions,Submisions[Submission Date]>= startdate &&
                                       Submisions[Submission Date]<= enddate  ) 
               )

    Try this.

     

     

    Thanks
    Raj

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    8 years ago

    Hi Manar,

    Based on my test, you could refer to below steps:

    Sample data:

    Create a measure:

    Current_Submission =
    var startdate= max(DimDate[Date])
    var enddate = min(DimDate[Date]) Return
    calculate(DISTINCTCOUNT(Submission[Number]),
    Filter (ALL(Submission), Submission[Date]<= startdate&&Submission[Date]>= enddate))

    Now you could see the result:

    You can also download the PBIX file to have a view.

    https://www.dropbox.com/s/t5zoiqxm1d6aqcx/Filter%20data%20using%20date%20range%20from%20slicer.pbix?dl=0

     

     

    Regards,

    Daniel He