DAX CALCULATE COUNTROWS with Date filter not working ...

[MoodyDirk]

O.K., this is embarrasing, but this DAX measure ...

HeadCount =

CALCULATE(COUNTROWS('FACT_employees'),

'FACT_employees'[started] <= 12/15/2021

)

... does not return any results. FACT_employees[started] is a Data type Date, Format mm/dd/yyyy.

This works fine ...

HeadCount =

CALCULATE(COUNTROWS('FACT_employees'),

'FACT_employees'[status] = "Active"

)

... FACT_employees[status] is a Data type Text, Format Text.

Any feedback, ideas, on why does this does not work for a Date type column is highly appreciated.

Googling this for a day, tried all sorts of different ways to counts rows with a date filter, but none is working.

I'm working with PowerBI Desktop September 2022, Version 2.109.642.0 64-bit.

Doing this ...

select
*
from dbo.FACT_employees_view
where

(status = 'active' and started <= '2021-12-15')

... on MS SQL and it works fine, but here the started column is of text format, changed to Date in PowerBI Column Tools.

Dirk

[AnthonyJoseph · Accepted solution]

Hi   @MoodyDirk

I think you should wrap the date coumn with the date function for comparision, so the measure would look like

HeadCount = CALCULATE(COUNTROWS('FACT_employees'), 'FACT_employees'[started] <= date(2021,12,15) )

This should work but let us know otherwise...

Thanks,

AnthonyJoseph

[MoodyDirk]

Yes, this works,

Thank You, Anthony.