cancel
Showing results for
Did you mean:
Memorable Member

## filter by actual date

Table A

PERIOD Country Purchases

 1/1/2016 France 150 1/1/2016 Italy 100 1/1/2016 Belgium 250 1/1/2017 France 55

below are a series of Measures - where I've just altered the date parameter.  The return value measure in the Card visual has me baffled....

Measure = 4

ActiveByDate = CALCULATE (
COUNTROWS('Table A'),
FILTER('Table A',
'Table A'[PERIOD] > 1/1/2016))

Measure = blank

ActiveByDate = CALCULATE (
COUNTROWS('Table A'),
FILTER('Table A',
'Table A'[PERIOD] = 1/1/2017))

Measure = 4

ActiveByDate = CALCULATE (
COUNTROWS('Table A'),
FILTER('Table A',
'Table A'[PERIOD] > 1/1/2017))

1 ACCEPTED SOLUTION
Community Champion

1) since you're using CALCULATE, you don't need the explicit FILTER

2) If you don't convert the literal string 1/1/2016 to a date by use of DATE() (e.g. DATE(2016,1,1)) then PowerBI interprets it as "1 divided by 1 divided by 2016", giving a number value that converts to Dec 30, 1899 (I believe - somewhere around there).  Try

```ActiveByDate = calculate(
countrows('Table A'),
'Table A'[Period] > date(2016, 1, 1)  //or = or <, etc
)```

Hope this helps.

David

4 REPLIES 4
Community Champion

1) since you're using CALCULATE, you don't need the explicit FILTER

2) If you don't convert the literal string 1/1/2016 to a date by use of DATE() (e.g. DATE(2016,1,1)) then PowerBI interprets it as "1 divided by 1 divided by 2016", giving a number value that converts to Dec 30, 1899 (I believe - somewhere around there).  Try

```ActiveByDate = calculate(
countrows('Table A'),
'Table A'[Period] > date(2016, 1, 1)  //or = or <, etc
)```

Hope this helps.

David

Memorable Member

thanks....  a literal date entry I rarely use in a DAX statement... usually refering to a Date Field....

so it wasn't clear to me as to whether one wrapped/bracketed a date field to indicate it as a date or whether these days in Power BI it would recognize the general format in context to the field it was being compared.....

since it wasn't throwing an error but just giving me an odd result - I was perplexed....  surprisingly I spent alot of time searching for a literal date entry example and couldn't seem to find one.....

Community Champion

Yeah, I sometimes have difficulty getting out of "SQL Server" mindset and into DAX mindset.  Dates and Booleans are not as simple in DAX / PowerBI.

Microsoft

So what are you trying to achieve?  Are you just trying to find the date of the most recent purchase for each Country?

Proud to be a Datanaut!

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors