Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CahabaData
Memorable Member
Memorable Member

filter by actual date

Table A

PERIOD Country Purchases

1/1/2016France150
1/1/2016Italy100
1/1/2016Belgium250
1/1/2017France55

 

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

 

 

www.CahabaData.com
1 ACCEPTED SOLUTION
dedelman_clng
Community Champion
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

View solution in original post

4 REPLIES 4
dedelman_clng
Community Champion
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

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

www.CahabaData.com

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. Smiley Happy

Phil_Seamark
Employee
Employee

HI @CahabaData,

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.