Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

How to filter based on an input date

Hello,

 

I would like to filter my data based on a date input. Create a slider like to determine a day in the month and use that value inside of measures.

How could I create that (the input & the ""variable"") ?

 

Thanks for your help

Need Help
Tutorial Requests

6 Replies

  • Jeanxyz's avatar
    Jeanxyz
    Power Participant
    4 years ago

    Do you have a column called 'input date' in the input table? If that's the case, you can create a dim_calendar table and link the input table to dim_calendar table based on relationship dim_calendar [date] - fact_input[input date]. 

     

    You can then add a slicer to the visual, drag and drop dim_calendar[date] to the slicer. 

     

    pbix sample: https://www.dropbox.com/s/8nzzp6mxlt4871x/filter%20test.pbix?dl=0

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I do have 2 dates columns that I want to filter, but I want to filter both of them but only the day.

      Would there be any way to do it ?

      • Jeanxyz's avatar
        Jeanxyz
        Power Participant
        4 years ago

        @fzpokg, Are both columns in the same table?  It's not a typical solution, but you can link both date columns to dim_calendar table, but Power BI only allows one active relationship, the other one will be inactive. You can enable the inactive relationship via userelationship(). 

         

        Here is DAX guide on userelationship()

        https://dax.guide/userelationship/

         

         

         

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Anonymous You would put a Date column into a Slicer visual. I believe that this is going to default to a slider where you can select a start and end time but you can change this using the little down caret icon in the upper right. Then, you can use this DAX to get the date/dates:

    VAR __Min = MIN('Dates'[Date])

    VAR __Max = MAX('Dates'[Date])

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you for the answer Greg_Deckler .

       

      I am trying to apply this solution, created a new table and added dummy dates inside of a column.

       

      Here is the dax query that I am then using in order to try to filter the date:

       

      currentMonthPunchedHours = CALCULATE(sum(punches[hours]), and(True, and(YEAR(punches[punchIn]) * 12 + MONTH(punches[punchIn]) = YEAR(TODAY()) * 12 + MONTH(TODAY()), and(YEAR(punches[punchOut]) * 12 + MONTH(punches[punchOut]) = YEAR(TODAY()) * 12 + MONTH(TODAY()), DAY('measure stuff'[breakEnd]) > DAY(punches[punchIn])))))
       
      I am getting the error 'a function "placeholder" has been used in a True/False expression that is sued as a table filter expression. This is not allowed'.
       
      In the dax query above, punchIn is the date to be filtered on, and breakEnd is "Max..." as mentionned in your answer.
       
      Thanks for the help.