Forum Discussion

arthurnotaro's avatar
arthurnotaro
Frequent Visitor
9 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 som...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 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
    Icon for Memorable Member rankMemorable Member
    9 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
      Icon for Microsoft Employee rankMicrosoft Employee
      9 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

      • CahabaData's avatar
        CahabaData
        Icon for Memorable Member rankMemorable Member
        8 years ago

        thank you for educating me on separator variation.

         

        I happen to have/need an almost identical requirement - and so copied in the suggested code, along with the exact same sample data that was posted.

         

        It did not work for me, was all 0s.  I traced this back to the Calendar table.  If instead of using the Calendar table/field, I instead use the posted Table and Month field - then it works.  Not sure why the Calendar table reference returns 0 except perhaps that the Calendar table is daily while the posted table is month and there is some join issue I don't understand.

         

        Also in regard to a 2nd isue - I further experiment by simplifying the final IF statement to just bare bones:

         

         IF (ThisMonth <> 0 && LastMonth <> 0 && LastTwoMonth <> 0,1,0)

         

        This also worked fine (not related to the Calendar date issue).  It is not clear to me what is the benefit of CALCULATE and would welcome advice on this point.