DAX formula at the row level to count distinct events before and after...


  • Hi,

    I have a DAX challenge here :)

    For each row in a table, I have to count the number of distinct values appearing in BOTH the previous 30days and the next 30 days.

    imagine this:

    a customer get a product A the 2013-06-01

    I want to know how many other products the same customer bought in the previous 30days and in the next 30 days, has to be the same products before and after.

    so this:

    1, customer 1, product C , 2013-04-01

    2, customer 1, product D , 2013-05-15

    3, customer 1, product B , 2013-05-20 ==> I want the number 1 here

    4, customer 1, product A , 2013-06-01 ==> I want the number 2 here

    5, customer 1, product D , 2013-06-05 ==> I want the number 1 here

    6, customer 1, product B , 2013-06-06

    7, customer 1, product B , 2013-06-07 

    8, customer 1, product C , 2013-06-08

    so when the product A was sold, I want to know that 2 same products were bought in the 30 days before and 30 days after.

    if the customer get 2 times the same product before or after (like the product B bought 2 times after the product A), I don't care, only the distinct number of products is important.

    my table already have a sequence number per customer if this may help.

    so is it possible to calculate this in DAX?

    or is it better to calculate this in my view used to fill the model?

    thanks :)

    Saturday, July 27, 2013 1:29 AM


All replies