Forum Discussion

HamidBee's avatar
HamidBee
Icon for Power Participant rankPower Participant
4 years ago
Solved

How do I use a date filter for a measure?

I am trying to use a date filter for a simple measure. I have data for various days and months between 2020 and 2021 in a table. I'd like to apply a filter for only the year 2020. I already have one filter included and I'd like to add the date filter ontop of my current filter.

 

So I'm trying to count the rows for the letter A but only in the year 2020.

 

Count = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Letter]="A"))
 
Any help would be greatly appreciated.
 
Thank you.
 
 
Need Help
Show and Tell
Tips and Tricks
  • littlemojopuppy's avatar
    littlemojopuppy
    4 years ago

    Hi HamidBee.

     

    I'm assuming that you have a date table in your data model and that it is marked appropriately.  If so, this should be fairly easy with either the DATESBETWEEN or DATESINPERIOD functions.  Pick a function and insert the appropriate start and end dates as parameters.  Hope this helps!

5 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi HamidBee.

     

    I'm assuming that you have a date table in your data model and that it is marked appropriately.  If so, this should be fairly easy with either the DATESBETWEEN or DATESINPERIOD functions.  Pick a function and insert the appropriate start and end dates as parameters.  Hope this helps!

    • HamidBee's avatar
      HamidBee
      Icon for Power Participant rankPower Participant
      4 years ago

      Thank you, it worked like a charm. I haven't created a date table though I just used a date column from the same table. Here is an example code of what I used:

      Count=
      CALCULATE(
      COUNTROWS('Table'),
      FILTER('Table','Table'[Coumn]="Allowed"),
      (DATESBETWEEN('Table'[Date column],DATE(2020,01,01),DATE(2021,01,01)
      )))

      Correct me if I'm wrong but it seems the Filter function must come before the DATESBETWEEN function in the formula otherwise DAX gives an error. Do you know why this is?

      Thanks in advance
      • littlemojopuppy's avatar
        littlemojopuppy
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Hi HamidBee.  It's good practice to always have a date table in your data models.

         

        Why the error...not really sure and without the pbix would be hard to tell.  But I did put your formula through the DAX formatter from SQLBI and you have extra parentheses before DATESBETWEEN.

        Count =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER ( 'Table', 'Table'[Coumn] = "Allowed" ),
    (
        DATESBETWEEN (
            'Table'[Date column],
            DATE ( 2020, 01, 01 ),
            DATE ( 2021, 01, 01 )
        )
    )
)

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    You can simplify your measure to

    Count = CALCULATE(COUNTROWS('Table'),'Table'[Letter]="A")

    Create a Calendar Table and build a relationship (Many to One and Single) from the Date column of your Table to the Date column of the Calendar Table.  Write calculated column formulas in the Calendar table to extract Year, Month name and Month number.  Sort Month name by Month number in the Calendar Table.  Build slicers for Year and Month name.  Select a Year and Month in the slicers.

    Hope this helps.

    • littlemojopuppy's avatar
      littlemojopuppy
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Ashish_Mathur Nice to see that discussion from our meetings about people swooping in and offering multiple solutions from Super Users is being honored.  Does nothing more than confuse users and pad stats.  But good job!