cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Count of Dates

Dear All,

 

I am trying to count user wise distinct date but not able to do. I have datetime columns so my measure is not giving correct counts because of time in the date.

 

Team Working Days = CALCULATE(DISTINCTCOUNT(Activity_Detail[Activity_Insert_At]), ALLSELECTED(Activity_Detail[Activity_Insert_At]))
 
With this measure, counts is not correct. In filter , I have selected May but below counts is coming -
erro.png
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Count of Dates

@bhuprakash - Really difficult without the source data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

 

But, maybe count all of your rows of users and subtract this count? Just purely guessing at this point due to lack of information.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
Highlighted
Super User IV
Super User IV

Re: Count of Dates

@bhuprakash , I doubt you Activity_Insert_At has timestamp , Change the format to include timestamp and check

 

Create a date and use

Activity_Insert_Date = [Activity_Insert_At].Date



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IV
Super User IV

Re: Count of Dates

You could try:

 

Team Working Days = CALCULATE(DISTINCTCOUNT(Activity_Detail[Activity_Insert_At]), ALLSELECTED(Activity_Detail[Activity_Insert_At]))

 

Team Working Days = 

VAR __Table = 

  ADDCOLUMNS(

    ALLSELECTED(Activity_Detail[Activity_Insert_At]),

    "Date Only",INT(Activity_Detail[Activity_Insert_At])

  )

RETURN

  COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table,"Date",[Date Only])))

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper IV
Helper IV

Re: Count of Dates

@amitchandak @Greg_Deckler  thanks for ur help,

 

@Greg_Deckler - after using your measure , now 85 counts showing for all users -

erro.png

Highlighted
Super User IV
Super User IV

Re: Count of Dates

@bhuprakash - Is Activity_Insert_At your date/time column? Maybe just:

 

Team Working Days = 

VAR __Table = 

  ADDCOLUMNS(

    Activity_Detail[Activity_Insert_At],

    "Date Only",INT(Activity_Detail[Activity_Insert_At])

  )

RETURN

  COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table,"Date",[Date Only])))

 

 

If not, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper IV
Helper IV

Re: Count of Dates

Dear @Greg_Deckler ,

Now counts are coming perfectly fine. But it is coming for users who have done activity. 

I need those users details also who have not done any activity with "0" count.?

 

Kindly suggest me on this also. 🙂

Thanks Again for your help.

Highlighted
Super User IV
Super User IV

Re: Count of Dates

@bhuprakash - Really difficult without the source data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

 

But, maybe count all of your rows of users and subtract this count? Just purely guessing at this point due to lack of information.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Helper IV
Helper IV

Re: Count of Dates

Hi @Greg_Deckler 

Thanks for guideing me to raise a better complaint. Next time i will take care of it.

By the way, After adding "0" i the end , I am getting those users also who have not done any activity in the selected date range -

 

Thanks for helping me out in the same.

 

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Top Solution Authors
Top Kudoed Authors