How do I use a date filter for a measure?

[HamidBee]

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.

[Ashish_Mathur]

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]

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

[littlemojopuppy · Accepted solution]

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]

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]

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