Incorrect DISTINCTCOUNT value when counting unique dates in table

[nickappftna]

I've got a table showing a list of orderlines with SKUs, ordernumber, etc. I'm trying to eventually get to a measure showing the average number of orders per day, however my number of day calculation is being thrown off. 

To calculate the number of days, I'm creating a measure based off my "DATE" column (which shows DateTime):

Day Count = DISTINCTCOUNT(ORDERS[ORDERDATE].[DATE])

 I know I've got just over 1 year's worth of data, and in actuality, the true distinct count of dates should be 385, however this measure returns 730. At first I had thought that the timestamp was throwing this off, but If I'm understanding correctly, putting in ".[DATE]" should be counting just the date portion, not the times.

Any ideas? Thanks much in advance.

[davehus · Accepted solution]

Hi @nickappftna ,

Create a column datevalue(datecolumn)

Then do a distinct count on that column, it should work.

Did I help you today? Please accept my solution and hit the Kudos button.

[nickappftna]

Interesting. This does work -- thank you for the quick answer. Though I'm not sure I understand why my original (failed) solution does not work. Isn't the point of adding ".[DATE]" to specify that I only want to look at the date portion of the TimeStamp? ...as opposed to .[Day], .[Month], etc.?

[davehus]

It's actually not something I've used before and oddly enough there isn't much documentation on it on the MT website. I'll have to go digging into the dax guide to see.

If this has worked for you, please accept it as a solution 🙂 Thank you.