Forum Discussion

SchoolBoy's avatar
SchoolBoy
Frequent Visitor
8 years ago
Solved

Count and sum it up

Hello,


I have a little problem :) 

How can I calculate a total monthly occurrence of unique customer numbers?

For example: there is a sales table including invoice date, customer number, invoice number, quantities and price
A customer can buy items several times a day.

I need to calculate a total daily occurence of unique customers and then sum it up for months, years, etc.

 
For example:

 

day: 2018.01.01 customer: 00001 SKU: SKU00001 Invoice:000001

day: 2018.01.01 customer: 00001 SKU: SKU00055 Invoice:000001

day: 2018.01.01 customer: 00001 SKU: SKU00155 Invoice:000001

day: 2018.01.01 customer: 01011 SKU: SKU00003 Invoice:000032

day: 2018.01.01 customer: 01055 SKU: SKU00003 Invoice:000032

day: 2018.01.01 customer: 01055 SKU: SKU00003 Invoice:000134

day: 2018.01.01 customer: 01055 SKU: SKU00003 Invoice:000143

day: 2018.01.01 customer: 00001 SKU: SKU00001 Invoice:000001

day: 2018.01.02 customer: 00001 SKU: SKU00055 Invoice:000002

day: 2018.01.02 customer: 00002 SKU: SKU00155 Invoice:000001

day: 2018.01.02 customer: 01003 SKU: SKU00003 Invoice:000032

day: 2018.01.02 customer: 01023 SKU: SKU00003 Invoice:000032

day: 2018.01.02 customer: 01023 SKU: SKU00003 Invoice:000134

...

day: 2018.01.31 customer: 01023 SKU: SKU00003 Invoice:000134

etc.

 

First:

I should count occurrence of unique customer numbers
day 1 - 1145 unique customers
day 2 - 1123 ...
day 3 - 1560 ...
day 31 - 2130 ...

 

Next:

total monthly occurrence = day1 + day2 + day3 ... day31

 

How can I do it in an easy way?


Thanks :)

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi SchoolBoy

     

    Try this MEASURE

     

    Measure =
SUMX (
    VALUES ( Table1[Day] ),
    CALCULATE ( DISTINCTCOUNT ( Table1[Customer] ) )
)
    • SchoolBoy's avatar
      SchoolBoy
      Frequent Visitor
      8 years ago

      Thank you very much, it works very well! :)