New Customer (under certain conditions) - How to ? DAX
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: New Customer (under certain conditions) - How to ? DAX

  1. #1
    New Member
    Join Date
    Aug 2016
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy New Customer (under certain conditions) - How to ? DAX


    New Customer (under certain conditions) - How to ? DAX
    Hi Everyone,

    It have been struggling with this for 2 days..

    Case :

    1 Date table (with Full date, Year, Month)
    1 Product table (with Product = Product A, B, C, D, E....)
    1 Sales Table (with CustomerKey, Unit,...)

    X products (A, B, C, D,....).

    Pivot Table : Rows = CustomerKey ; Columns = Months

    1) I need to calculate the new customers for Product A. It can be done with the measure below (from SQLBI) when filtering the Pivot with Product A.

    However, I don't want to limit the scope to Product A cause I have got another condition to take into account.

    New Customer = at least 1 unit of Product A, and 0 unit of (Product B or Product C) since begining of the year.

    How can I adapt the measure here below to achieve what I want ?

    I tried to add another column to the ADDCOLUMN function (in the measure below) : YTD units of Product B and Product C and then filtering this new column to zero, but it does not help...

    Code:
    [New Customers] := 
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                VALUES ( Sales[CustomerKey] ),
                "PreviousSales", CALCULATE (
                    COUNTROWS ( Sales ),
                    FILTER (
                        ALL ( 'Date' ),
                        'Date'[FullDate] < MIN ( 'Date'[FullDate] )
                    )
                )
            ),
            [PreviousSales] = 0
        )
    )

    2) And then I would like to calculate how many units of product A these new customers purchased since begining of the year and not only for the month they became a new Customer

    Thanks a lot for your help

    Arnaud

  2. #2
    New Member
    Join Date
    Aug 2016
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: New Customer (under certain conditions) - How to ? DAX

      
    That's fine, I found out the solution.

    I added another column in the ADDCOLUMNS function that calculates if there were sales for Product B and C before or during the month in the row context of the pivot table and then filtered this new column to 0 with the Filter function surrounding the ADDCOLUMNS function.

    I also added a VALUES(Date(Year)) in both calculate functions in order to keep active my Year filter in my Pivot Table so what happened in 2016 is not taken into account for 2017.

    Cheers

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com