filter column with month and year only

[Anonymous]

I want filter column Start(Date) only month and year.

Not mind date.

How to edit query? 

VAR __CurrentMeasure = 

CALCULATE (

COUNT ( 'Before'[Start(Date)]),

FILTER (

'Before ',

'Before'[Start(Date)] = "15/1/2564"

)

)

RETURN

IF ( ISBLANK ( __CurrentMeasure ), 0, __CurrentMeasure )

[PhilipTreacy]

Hi @Anonymous 

You need to use DATEVALUE to convert those text values into proper dates

Measure = VAR __CurrentMeasure = 
CALCULATE (
COUNT ( 'Before'[Start(Date)]),
FILTER (
'Before',
AND ( YEAR(DATEVALUE('Before'[Start(Date)])) = 2021 , MONTH(DATEVALUE('Before'[Start(Date)])) = 12 )
)
)
RETURN
IF ( ISBLANK ( __CurrentMeasure ), 0, __CurrentMeasure )

Regards

Phil

[PhilipTreacy]

Hi @Anonymous 

Without seeing the data I can't do any more.  I don't know what these dates stored as text look like.  Are they 1 Dec 21, or 1st December 2021 or 1/12/2021 etc?

That's why you should always supply some sample data.

Can you not just change the column to Date type?

regards

Phil

[Anonymous]

@PhilipTreacy  Now I change format type to Date. 

But I use your query  but not found data.

 

It value in care show 0 

[PhilipTreacy]

@Anonymous 

Year needs to be a 4 digit number

Month is a number from 1 to 12

Phil

[Anonymous]

@PhilipTreacy  Change in format or measure?

Now format value dd/mm/yy

[PhilipTreacy · Accepted solution]

@Anonymous 

Just change the column back to text and use the code I gave you that works with dates stored as text

Measure = VAR __CurrentMeasure = 
CALCULATE (
COUNT ( 'Before'[Start(Date)]),
FILTER (
'Before',
AND ( YEAR(DATEVALUE('Before'[Start(Date)])) = 2021 , MONTH(DATEVALUE('Before'[Start(Date)])) = 12 )
)
)
RETURN
IF ( ISBLANK ( __CurrentMeasure ), 0, __CurrentMeasure )

Phil

[Anonymous]

@PhilipTreacy  If I change the requirement to not year=2021 , month not = 12 and column not blank measure as below.

It error

Measure 2 = VAR __CurrentMeasure =

CALCULATE (

COUNT ( 'Before'[Start(Date)]),

FILTER ('Before',

AND ( YEAR(DATEVALUE('Before'[Start(Date)])) <> 2021 , MONTH(DATEVALUE('Before '[Start (Date)])) <> 1 , 'Before '[Start(Date)] <> BLANK())

)

)

RETURN

IF ( ISBLANK ( __CurrentMeasure ), 0, __CurrentMeasure )

It error : Too many arguments were passed to the AND function. The maximum argument count for the function is 2.

[PhilipTreacy]

Hi @Anonymous 

OK, you can use this,

Measure = VAR __CurrentMeasure = 
CALCULATE (
COUNT ( 'Before'[Start(Date)]),
FILTER (
'Before',
AND ( YEAR('Before'[Start(Date)]) = 2021 , MONTH('Before'[Start(Date)]) = 12 )
)
)
RETURN
IF ( ISBLANK ( __CurrentMeasure ), 0, __CurrentMeasure )

Regards

Phil

[Anonymous]

@PhilipTreacy Value in start date are format text.

[PhilipTreacy]

Hi @Anonymous 

Download this sample PBIX file with the following data and code

So you just want to count the number of dates equal to (or before) a specific date?

Please, always supply some sample data and your expected result.

Using this data

To count the number of dates before 1 Dec 2021 you can use this

Measure = VAR __CurrentMeasure = 
CALCULATE (
COUNT ( 'Before'[Start(Date)]),
FILTER (
'Before',
'Before'[Start(Date)] < DATE(2021,12,1)
)
)
RETURN
IF ( ISBLANK ( __CurrentMeasure ), 0, __CurrentMeasure )

Regards

Phil

[Anonymous]

@PhilipTreacy I want to count the number specific december 2021 only , never mind date