Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

DAX to check Company and Company / Product exists

  • 1.  DAX to check Company and Company / Product exists

    Posted Sep 13, 2021 09:03 AM
    Hi,
    I'm currently working on PowerBI Desktop to provide some Customer/Product related Data. All works fine until a got the new requirement from the Sales guys
    to show new or existing to a customer or customer - Product relation

    I've this dax.do script
    (Script and result see below,  Data from Year 2007 to 2009)

    I struggle to add some columns
    1. new Customer       (first green arrow)
       In the example below. I need to know that in the Year 2008 the customer 'AmsterdamCompany' is a new Customer
       as now data before 2008 for the company exists

    2. new Product to existing Customer (red arrow)
       the same Customer bought a new product in  2009

    3. additional buy  same Product to existing Customer   (second green arrow)
       the same Customer bought a new product in  2009

    Could somebody give me a hint how to solve this


    DEFINE
        MEASURE Sales[Sales Amount] =
            SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
        MEASURE Sales[AVERAGE Monthly Sales] =
            VAR MonthlySales =
                ADDCOLUMNS (
                    DISTINCT ( 'Date'[Calendar Year Month] ),
                    "@MonthlySales", [Sales Amount]
                )
            VAR FilteredSales =
                -- Iterator required to filter the @MonthlySales column        
                FILTER ( MonthlySales, [@MonthlySales] > 0 )
            VAR Result =
                AVERAGEX ( FilteredSales, [@MonthlySales] )
            RETURN
                Result
    EVALUATE
    SUMMARIZECOLUMNS (
        Customer[Company Name],
        'Product'[Brand],
        'Date'[Calendar Year],
    --   'Product'[Color],
      
               "count", DISTINCTCOUNT (Sales[CustomerKey]),
             "sumcount", COUNT (Sales[CustomerKey]),
               
        "Sales Amount",    Sales[Sales Amount],     
        "AVERAGE Monthly Sales", [AVERAGE Monthly Sales]
    )
    order by  Customer[Company Name],
     'Date'[Calendar Year]​
    The Result
    Dax Result



    ------------------------------
    DA

    ------------------------------


  • 2.  RE: DAX to check Company and Company / Product exists

    Posted Sep 13, 2021 11:03 AM
    Hi @Dirk Anacker,

    See if this gets you on track. Just use these measures to create the additional logic you require.

    MEASURE Sales[Date New Customer] =
    CALCULATE(
    MIN( Sales[Order Date] ),
    ALLEXCEPT( Sales, Sales[CustomerKey], Customer )
    )

    MEASURE Sales[Date New Product/Customer] =
    CALCULATE(
    MIN( Sales[Order Date] ),
    ALLEXCEPT( Sales, Sales[CustomerKey], Sales[ProductKey], Customer, Product )
    )​

    I hope this is helpful


    ------------------------------
    Melissa de Korte
    ------------------------------



  • 3.  RE: DAX to check Company and Company / Product exists

    Posted Sep 14, 2021 03:54 AM
    Hello Melissa,
    thank you so much. It works :-)
    Sometimes it is so easy, you won't find the solution by your self.
    I just have to add an additional filter to the result to get rid of the 'blanks'
    Regards
    Dirk

    ------------------------------
    DA
    ------------------------------