Problems with Distinct Count

[arthurnotaro]

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. 

[v-jiascu-msft]

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]

@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]

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]

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.

[v-jiascu-msft]

Hi @CahabaData,

Did you mean the date table "Calendar"? The time intelligence functions need a date table. 

Best Regards!

Dale