How to calculate count in date and time column?

[bourne2000]

Hi

I have below Date/time column

Date/Time

1.1.21 1:52:10 PM

1.1.21 1:52:10 PM

1.1.21 1:52:10 PM

1.1.21 1:52:10 PM

1.1.21 1:52:10 PM

1.1.21 1:52:10 PM

1.1.21 1:52:10 PM

1.1.21 1:55:10 PM

1.1.21 1:55:10 PM

1.1.21 1:55:10 PM

1.1.21 2:00:10 PM

1.1.21 2:00:10 PM

1.1.21 2:00:10 PM

1.1.21 2:00:10 PM

1.1.21 2:00:10 PM

1.1.21 2:00:10 PM

1.1.21 2:00:10 PM

1.1.21 2:00:10 PM

2.1.21 1:00:10 PM

2.1.21 1:00:10 PM

2.1.21 1:00:10 PM

2.1.21 1:00:10 PM

2.1.21 2:00:10 PM

2.1.21 3:00:10 PM

I need to count, how many records are present on each date. Need output as expected

Date/Time	Count
1.1.21	18
2.1.21	6

My data is huge and it's direct query. Can anyone advise how to calculate the count?

[bcdobbs]

I think if you create a calculated column that's directly equal to your existing one.

eg New Column = TableName[Original Column]

you can set it's data type to "date".

At that point COUNTROWS should do the job.

(Not by a computer so think you need the calculated column step because it's direct query but might work without it)

[bourne2000]

@bcdobbs I tried it's not working

It's coming individual

Can you please advise

[bcdobbs]

I think you've formatted it as date rather than actually changing the data type. Same modelling tab but far left.

[bourne2000]

Not sure whether I am doing correct

Please advise

[bcdobbs]

That looks like it should now be correct. Will grab my laptop and have a go. Fairly sure that should have worked. Will get back to you asap

[bcdobbs · Accepted solution]

That's really odd! 

Try creating the calculated column as follows:

DateColumn = INT ( TestTable[DateTimeColumn] )

It will default to a "Whole Number" data type. Set it back to "Date" and then use it in your visual it then all seems to work:

 I think it must be something to do with how direct query is folding back to source. Will look into it some more as don't have a good answer at the moment.

[bcdobbs]

Doing it my original way the conversion to date wasn't being folded back to source (you can see the SQL query if you go to View >> Performance Analyser.

My INT suggestion will work but a better way would be to change the datatype in Power Query:

[bourne2000]

@bcdobbs  my data is big, I am using a direct query. Unable to change in Power Query. Any other method?

[bcdobbs]

I was testing with a direct query connection. Direct query runs through an power query layer only certain transforms are possible but that one works.

[bourne2000]

thanks. I am attaching the sample pbix file

https://we.tl/t-IyAK3QmTgW

Thanks for your help