Forum Discussion
Count unique days when days have multiple entries
I have daily sales data, and I want to count how many days a given sales person made a sale. However, they could make two sales on a given day, and will therefore have two lines. As a for instance, if someone makes a single sale on each day of March, but on March 31 makes two sales, my function is returning 32. I'm currently using a calculate/countrows function structure, but is there a way to only count the unique values when sales occur? Obviously there aren't 32 days in March, but I don't want to use a min/max function because I only want to give credit for the days with actual activity. Does this make sense? Any help is appreciated.
- Anonymous5 years ago
This measure works with the below data set. It will work with a date table too:
Unique Date Count =CALCULATE(DISTINCTCOUNT(Sheet1[Date]),FILTER(Sheet1,Sheet1[SaleAmount]<>0))Date SaleAmount 3/1/2021 100 3/2/2021 200 3/3/2021 300 3/4/2021 400 3/5/2021 500 3/6/2021 600 3/7/2021 700 3/8/2021 800 3/9/2021 900 3/10/2021 1000 3/11/2021 1100 3/12/2021 1200 3/13/2021 1300 3/14/2021 1400 3/15/2021 1500 3/16/2021 1600 3/17/2021 1700 3/18/2021 1800 3/19/2021 1900 3/20/2021 2000 3/21/2021 2100 3/22/2021 2200 3/23/2021 2300 3/24/2021 2400 3/25/2021 2500 3/26/2021 2600 3/27/2021 2700 3/28/2021 2800 3/29/2021 2900 3/30/2021 3000 3/31/2021 3100 3/31/2021 3200
4 Replies
- Ashish_Mathur
Super User
Hi,
Does this measure work?
=COUNTROWS(Filter(values(Calendar[date]),[total sale]>0))
- AnonymousNot applicable
I couldn't quite get this to work with my particular dataset, but I know how tricky it is when you don't have the actual info to reference. I really appreciate the response and will be experimenting with this formula to see where I can leverage the logic. Thanks again!
- AnonymousNot applicable
This measure works with the below data set. It will work with a date table too:
Unique Date Count =CALCULATE(DISTINCTCOUNT(Sheet1[Date]),FILTER(Sheet1,Sheet1[SaleAmount]<>0))Date SaleAmount 3/1/2021 100 3/2/2021 200 3/3/2021 300 3/4/2021 400 3/5/2021 500 3/6/2021 600 3/7/2021 700 3/8/2021 800 3/9/2021 900 3/10/2021 1000 3/11/2021 1100 3/12/2021 1200 3/13/2021 1300 3/14/2021 1400 3/15/2021 1500 3/16/2021 1600 3/17/2021 1700 3/18/2021 1800 3/19/2021 1900 3/20/2021 2000 3/21/2021 2100 3/22/2021 2200 3/23/2021 2300 3/24/2021 2400 3/25/2021 2500 3/26/2021 2600 3/27/2021 2700 3/28/2021 2800 3/29/2021 2900 3/30/2021 3000 3/31/2021 3100 3/31/2021 3200 - AnonymousNot applicable
Exactly what I needed. Thank you so much!