Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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.

 

 

 

  • Anonymous's avatar
    Anonymous
    5 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
    )
    )

     

    DateSaleAmount
    3/1/2021100
    3/2/2021200
    3/3/2021300
    3/4/2021400
    3/5/2021500
    3/6/2021600
    3/7/2021700
    3/8/2021800
    3/9/2021900
    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

  • Hi,

    Does this measure work?

    =COUNTROWS(Filter(values(Calendar[date]),[total sale]>0))

    • Anonymous's avatar
      Anonymous
      Not 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!

  • Anonymous's avatar
    Anonymous
    Not 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
    )
    )

     

    DateSaleAmount
    3/1/2021100
    3/2/2021200
    3/3/2021300
    3/4/2021400
    3/5/2021500
    3/6/2021600
    3/7/2021700
    3/8/2021800
    3/9/2021900
    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Exactly what I needed. Thank you so much!