Forum Discussion

Carlaf's avatar
Carlaf
Frequent Visitor
4 years ago
Solved

count distinct

Good morning,

 

I'm trying to calculate the number of customer's that bought something this and the last year.

 

I was able to calculate the customer's of this year like this.. 

 

customer's_today = calculate(DISTINCTCOUNT(CUSTOMERS_DETAILS[CUSTOMER]), datesytd(STAT_VEN[TMPDDATA]),filter (STAT_VEN,STAT_VEN[sales_this_year]>0 ))
 
How can i do it?
Thanks

3 Replies

  • Another method would be to use the time column of your data as a slicer, select for the current year.

    Meanwhile, place your customers in the visual (table for exemple) and select count (distinct) in the summarization pannel :

     

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Carlaf 

     

    It's not clear where you want to place these two measures for comparison.

    Suppose we have a table like this.

    1. If placed in a same visual for comparison, you can use [Year] as the axis and put the measure [customer's_today] in the value field.

    2. If you want to compare this year's value with last year's value and compare the measures in two different visualizations, please try.

     

    customer's_last_year1 = 
    CALCULATE (
        DISTINCTCOUNT ( CUSTOMERS_DETAILS[CUSTOMER] ),
        SAMEPERIODLASTYEAR ( DATESYTD ( STAT_VEN[TMPDDATA] ) ),
        FILTER ( STAT_VEN, STAT_VEN[sales_this_year] > 0 )
    )

     

    or

     

    customer's_last_year2 = 
    CALCULATE (
        DISTINCTCOUNT ( CUSTOMERS_DETAILS[CUSTOMER] ),
        PARALLELPERIOD('STAT_VEN'[TMPDDATA],-1,YEAR),
        FILTER ( STAT_VEN, STAT_VEN[sales_this_year] > 0 )
    )

     

    3.  Comparison of the same period.

     

    customer's_last_year3 = 
    VAR _last_date =
        LASTDATE ( 'CUSTOMERS_DETAILS'[DateCloumn] )
    VAR _result =
        CALCULATE (
            DISTINCTCOUNT ( CUSTOMERS_DETAILS[CUSTOMER] ),
            SAMEPERIODLASTYEAR ( DATESYTD ( _last_date ) ),
            FILTER ( STAT_VEN, STAT_VEN[sales_this_year] > 0 )
        )
    RETURN
        _result

     

    Best Regards,
    Gao

    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data