Forum Discussion

gco's avatar
gco
Resolver II
3 years ago

Date Filter range

Hello there,

I have not used PowerBI in a while and is now trying to relearn it. 

 

Can someone please tell me how to create a date parameter range?

So i have a set of data with 3 columns, person, date1, and date2.  The date parameter will allow users to pull the list of persons with

(date1 >= @dateparameterstart and date1 <=dateparameterend) or (date2 >= @dateparameterstart and date2 <= dateparameterend).

Essentially i would like to include person if the date1 or date2 is within the date parameter range.

Thank you in advance


Glen

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    gco , Create a calendar table and do NOT join it with any date . Code at the end

     

    Then create a measure like

     

    new measure =
    var _max = maxx(allselected(Date),Date[Date])
    var _min = minx(allselected(Date),Date[Date])
    return
    calculate( sum(Table[Value]), filter('Table', ('Table'[date1] >=_min && 'Table'[date1] <=_max ) || ('Table'[date2] >=_min && 'Table'[date2] <=_max) ))

     

     

    Calendar code, Date Table (Mark this as date table). Use in slicer

     

     

    Date= Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
    , "Year", year([date])
    , "Month Year", format([date],"mmm-yyyy")
    , "Month year sort", year([date])*100 + month([date])
    , "Qtr Year", format([date],"yyyy-\QQ")
    , "Qtr", quarter([date])
    , "Month",FORMAT([Date],"mmmm")
    , "Month sort", month([DAte])
    , "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
    , "Month Type", Switch( True(),
    eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
    eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
    Format([Date],"MMM-YYYY") )
    ,"Year Type" , Switch( True(),
    year([Date])= year(Today()),"This Year" ,
    year([Date])= year(Today())-1,"Last Year" ,
    Format([Date],"YYYY")
    )

     

     

    • gco's avatar
      gco
      Resolver II
      3 years ago

      Hi amitchandak , 

       

      Thank you for the code.  For some reason though, it is not giving me what i want.  Not sure if it is user error.  But i created a similar code.  And it works if i put the DateFilter measure into the filter and set it to 1.  I think i just have to test your code further.  And sorry, i am not sure what the SUM(Table[Value]) does in your measure.

      DateFilter = var min1= MIN('Calendar'[FullDate]) var max1=MAX('Calendar'[FullDate])
       
      return IF((MAX('Table'[ColumnDate1])>=min1&&MAX('Table'[ColumnDate1])<=max1)||(MAX('Table'[ColumnDate2])>=min1&&MAX('Table'[ColumnDate2])<=max1),1,0)
    • gco's avatar
      gco
      Resolver II
      3 years ago

      And say for example, i have the data.

       

      Branch,account,date1,date2,datefilter

      123,12345,2022-01-01,2022-10-11,1

      123,12345,2022-01-01,2022-10-11,1

      345,34565,2022-01-01,NULL,1

      434,2234,NULL,2022-10-11,1

      434,4456,NULL,2022-01-01,1

       

      How can i do the distinct count by branch?  So like if the datefilter = 1 (meaning they are within the date slider), i want my table to show.

       

      Branch,date1count,date2count

      123,1,1

      345,1,0

      434,0,2