DistinctCount on date field returns number of days in calendar year instead of number in dataset

[larsvillaume]

Hi,

I have imported a csv file as dataset. The dataset contains incident records for 6 months and it has date field of type date. I need a number of days in the dataset i.e. a number of days that has an incident record. I have tried to create a measure using DISTINCTCOUNT but it returns 365 as value i.e. the number of days in the calendar year instead of just the dates for the incident records that exist in the dateset (6 months) .

"Days with incidents = DISTINCTCOUNT(('DataSet All Incidents Last 6 Months'[Created Date].[Date]))"

I have tried using FILTER to only include rows from the dateset but that doesn't give the right result either.

br

Lars

[Mariusz · Accepted solution]

Hi @larsvillaume 

Try the below, without .[Date]

Days with incidents = DISTINCTCOUNT( 'DataSet All Incidents Last 6 Months'[Created Date] )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Mariusz · Accepted solution]

Hi @larsvillaume 

Try the below, without .[Date]

Days with incidents = DISTINCTCOUNT( 'DataSet All Incidents Last 6 Months'[Created Date] )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[larsvillaume]

Hi @Mariusz 

Thanks but that (without .[date]) gives a number equal to number of rows in the dataset. The dataset looks like this in query editor. The Created Date type is of "Date/Time". There incidents is from may to november so if incidents is registered every day in those 6 months I should get a number of around 180 distinct days.

br

Lars

[Mariusz · Accepted solution]

Hi @larsvillaume 

When you referring to .[date] you are in fact referring to a hidden date dimension that will create a min of a full year that's why you see 365, to avoid it you can split you DateTime in the Query Editor or model and then count distinct on the date field

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[larsvillaume]

Thx @Mariusz ,

I duplicated the "Created Date" column and changed the type to just Date, so now I get the right number. I guess using split in the quey is a more proper ways of acheiving the goal so will try that also.

br

Lars