Forum Discussion

arthurnotaro's avatar
arthurnotaro
Frequent Visitor
8 years ago

Problems with Distinct Count

I have a table with the following information

 

Client code, Product line, Product code, Month, Sales amount

 

My goal is to find out the number of clients (distinctcount) that have purchased something (any amount) every month in the last three months (selected month + 2 previous months). I also should be able to filter by month, product line and product code. 

 

 

Example: 

 

Client code, Product line, Product code, Month, Sales amount

123, ProductLine1, 10, august 2017, 100

123, ProductLine1, 11, august 2017, 10

123, ProductLine2, 10, july 2017, 30

123, ProductLine1, 10, june 2017, 60

234, ProductLine2, 10, august 2017, 30

234, ProductLine1, 10, june 2017, 50

 

In this example, if I selected august it should return 1, because only client 123 had purchases on august, july and june.

 

 

 

I tried the measure below, but the the distinct count doesn't seem to be working.

 

ClientCount = var m0=sum(Table[SalesAmount])
var m1 = CALCULATE(SUM(Table[SalesAmount]);PARALLELPERIOD(Table[Date];-1;MONTH))
var m2 = CALCULATE(SUM(Table[SalesAmount]);PARALLELPERIOD(Table[Date];-2;MONTH))
return
CALCULATE(DISTINCTCOUNT(Table[Client Code]); FILTER(Table;m0>0);FILTER(Table;m1>0);FILTER(Table;m2>0) )

 

Do you guys have any ideas?

 

Than you in advance. 

Need Help

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi arthurnotaro,

     

    Firstly we need to create a date table in case there are missed months. Create a relationship.

    Calendar =
CALENDAR ( DATE ( 2017; 1; 1 ); DATE ( 2017; 12; 31 ) )

    Secondly, try this formula.

    Measure  =
VAR ThisMonth =
    DISTINCTCOUNT ( Table1[Client code] )
VAR LastMonth =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Client code] );
        PREVIOUSMONTH ( 'Calendar'[Date] )
    )
VAR LastTwoMonth =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Client code] );
        PARALLELPERIOD ( 'Calendar'[Date]; -2; MONTH )
    )
RETURN
    IF (
        ThisMonth <> 0
            && LastMonth <> 0
            && LastTwoMonth <> 0;
        CALCULATE (
            DISTINCTCOUNT ( Table1[Client code] );
            DATESINPERIOD (
                'Calendar'[Date];
                EOMONTH ( MIN ( 'Calendar'[Date] ); 0 );
                -3;
                MONTH
            )
        );
        0
    )

    Finally, create a visual. The month and client should be in the visual. 

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

     

     

    • CahabaData's avatar
      CahabaData
      Memorable Member
      8 years ago

      v-jiascu-msft

       

      did not work for me - returned all 0

       

      I note the first This Month VAR of current month has no date range and so is going to return a distinct list even if a client is not active in current month

       

      also am wonder why the use of ';' symbols thru out rather than ','    my intellisense did not like the semi-colon.......

       

       
      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi CahabaData,

         

        Do you have the similar scenario? The "This Month" has a context "current month" in the visual. So no date range is needed. Besides, the fields of the visual are important. They should be from the proper table. Try it again please.

        ";" is a separator for some countries. 

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        BTW, if you have any special questions, please open a new thread.

         

        Best Regards!

        Dale