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 ...
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!

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