Noob question about using the Calculate and DistinctCount functions and dates

[Anonymous]

Hi all,

New to PBI and have started working on few projects and so far am a big fan of the product but I'm new and have a think is a simple quesiton but I'm stuck and seeking some help.

Objective -  I want to create a Measure that counts the number of distinct files, with a certain status after a certain date.

Attempt so far -

CNT files - Closed 22/23 FY = CALCULATE(

DISTINCTCOUNT('TABLE'[ID]), 'TABLE'[File status] = "Closed",'TABLE'[Date Closed] > 01/07/2022)

It works fine with just the File status filter but when I add the date filter I return no results yet I know there are many in the database.

All assistance would be great.

Cheers

Phil

[tamerj1 · Accepted solution]

Hi @Anonymous 
Please try

CNT files - Closed 22/23 FY =
CALCULATE (
    DISTINCTCOUNT ( 'TABLE'[ID] ),
    'TABLE'[File status] = "Closed",
    'TABLE'[Date Closed] > DATE ( 2022, 7, 1 )
)

[Anonymous]

@tamerj1Thank you, that works perfectly.

Can I ask, when using dates in the Calculate function is the above layout "DATE (yyyy, mm, DD)" the only way to write them? Just asing as this will be something  will need to do 100's of times.

Cheers

Phil

[amitchandak]

@Anonymous , if you are adding a close date filter, that is less than 1-jul-2022, this measure will not give any value as that value has been hard coded for > filter

[Anonymous]

@amitchandakThank you for the reply.

I'm actually only seeking a count of files that have been closed in this Finacial Year i.e. 01/07/2022 through to 30/06/2023.

If I use the "Filters on this visual" function I get the result i'm seeking, just can't seem to get a measure to achieve the same result.

Any other ideas.

Cheers

Phil