Forum Discussion

powerbiexpert22's avatar
powerbiexpert22
Icon for Impactful Individual rankImpactful Individual
2 years ago
Solved

new customers

i need to find customers who never placed order before given date

 

example vishal is new customers on date 10-06-2002 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi powerbiexpert22 

     

    Thanks for the reply from Jihwan_Kim , please allow me to provide another insight:

    I create a set of sample data, and add a calculate column on order table:

     

    customername = RELATED(customer[name])

     

     

    Then add a calender table:

     

    Table = CALENDARAUTO()

     

    Then create a measure:

     

    MEASURE =
VAR _getdate =
    MAX ( 'Table'[Date] )
VAR _getname =
    FILTER (
        SUMMARIZE (
            ADDCOLUMNS (
                'order',
                "_mindate",
                    CALCULATE (
                        MIN ( 'order'[orderdate] ),
                        FILTER (
                            ALL ( 'order' ),
                            'order'[customerid] = EARLIER ( 'order'[customerid] )
                        )
                    )
            ),
            [customername],
            [_mindate]
        ),
        [_mindate] >= _getdate
    )
RETURN
    CONCATENATEX ( _getname, [customername], "; " )

     

    Then add a slicer:

    And a card:

    You can select the date from the slicer, and the outcome is the customername who never placed order before the selected date, the result is as follow:

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    I do not know how your expected outcome looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

     

     

    no order customers before the period: = 
COUNTROWS (
    FILTER ( VALUES ( customer[customer_id] ), ISBLANK ( [Sales by order date:] ) )
)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi powerbiexpert22 

     

    Thanks for the reply from Jihwan_Kim , please allow me to provide another insight:

    I create a set of sample data, and add a calculate column on order table:

     

    customername = RELATED(customer[name])

     

     

    Then add a calender table:

     

    Table = CALENDARAUTO()

     

    Then create a measure:

     

    MEASURE =
VAR _getdate =
    MAX ( 'Table'[Date] )
VAR _getname =
    FILTER (
        SUMMARIZE (
            ADDCOLUMNS (
                'order',
                "_mindate",
                    CALCULATE (
                        MIN ( 'order'[orderdate] ),
                        FILTER (
                            ALL ( 'order' ),
                            'order'[customerid] = EARLIER ( 'order'[customerid] )
                        )
                    )
            ),
            [customername],
            [_mindate]
        ),
        [_mindate] >= _getdate
    )
RETURN
    CONCATENATEX ( _getname, [customername], "; " )

     

    Then add a slicer:

    And a card:

    You can select the date from the slicer, and the outcome is the customername who never placed order before the selected date, the result is as follow:

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Raj341's avatar
    Raj341
    Frequent Visitor
    2 years ago

    Hi powerbiexpert22 
    CC: Jihwan_Kim Anonymous 
    I have the same issue, is your issue resolved?
    I am trying to fix the similar issue.
    Data looks almost similar to your excel. I created the bins like 0-2, 2-4 etc, through dax. now I need to insert the values of customers count. but i am always getting aggregate value. if your issue is resolved please help me to fix it
    Measure1:

    Customer_count=
    VAR _MinDate = Table[Min_Order_Date]
    VAR _MaxDate = Table[Max_Order_Date]

    RETURN
    CALCULATE(
    DISTINCTCOUNT(Table[OrderNo]),
    Table[OrderDate] >= _MinDate && Table[OrderDate] <= _MaxDate,
    ALLEXCEPT(Table, Table[CustomerID])

    Column1: 
    Customer_Order_Bucket =
    SWITCH(
    TRUE(),
    [Customer_count] = 1, "1",
    [Customer_count] >= 2 && [Customer_count] <= 4, "2-4",
    [Customer_count] >= 5 && [Customer_count] <= 6, "5-8",
    [Customer_count] >= 9, "9+",
    "Other"
    But still I am getting the results like below

    Please help me where i am doing wrong