topic Re: Noob question about using the Calculate and DistinctCount functions and dates in DAX Commands and Tips

Noob question about using the Calculate and DistinctCount functions and dates

Anonymous — Wed, 03 Aug 2022 14:14:06 GMT

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

Re: Noob question about using the Calculate and DistinctCount functions and dates

amitchandak — Wed, 03 Aug 2022 15:16:36 GMT

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

Re: Noob question about using the Calculate and DistinctCount functions and dates

Anonymous — Wed, 03 Aug 2022 15:23:54 GMT

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

Re: Noob question about using the Calculate and DistinctCount functions and dates

tamerj1 — Wed, 03 Aug 2022 16:16:00 GMT

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

Re: Noob question about using the Calculate and DistinctCount functions and dates

Anonymous — Thu, 04 Aug 2022 01:54:23 GMT

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