Forum Discussion

AlexanderBP's avatar
AlexanderBP
Frequent Visitor
6 years ago
Solved

Sumx and datesbetween two date columns

Hello community 

 

I'm having trouble creating a column that sums up the turnover for each customer created within the first 20 days of his first purchase. 

 

I have added a picture and a google sheets example of the data ( the yellow column is what I'm trying to create).
My dax so far looks like this:

 

20DaysPurchases = SUMX(
  FILTER(Table[customer id]=EARLIER(Table[customer id]) &&
   DATESBETWEEN(Table[Purchase Date];(Table[Customer Added Date]-20);Table[Customer Added Date]))
     ;Table[Turnover])
 
It returns the error "A date column containing duplicate dates was specified in the call to function 'DATESBETWEEN'. This is not supported."

 

 

 


https://docs.google.com/spreadsheets/d/1EQ5-fy2Q66skkIX4aP6RJnltIdeb5OUVYX_SaHg_swg/edit?usp=sharing

Thank you in advance

Need Help
  • richbenmintz's avatar
    richbenmintz
    6 years ago

    Hi AlexanderBP,

     

    Please try this column formula, it results in the following table based on your sample data. 

     

    20DaysPurchases = 
    var custId = [Customer id]
    var firstPurchaseDate  = CALCULATE(MIN([Customer Added Date]), filter(ALL('Table'), 'Table'[Customer id] = custId))+20
return 
    CALCULATE(SUM([Turnover]), FILTER(ALL('Table'), 'Table'[Purchase Date] <= firstPurchaseDate && 'Table'[Customer id] = custId))

     

     

    Hope this Helps,
    Richard
    Did I answer your question? Mark my post as a solution!
    Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

     

  • Icey's avatar
    Icey
    6 years ago

    Hi AlexanderBP ,

     

    The below column works in your scenario, too. The calculation logic is similar to that of richbenmintz.

    And if you create it as a measure, it also works.

     

    20DaysPurchases = 
VAR FirstPurchase =
    CALCULATE (
        MIN ( 'Table'[Purchase Date] ),
        ALLEXCEPT ( 'Table', 'Table'[Customer id] )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Turnover] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Customer id] ),
            'Table'[Purchase Date] <= FirstPurchase + 20
        )
    )

     

     

    You can check more details from here.

     

     

    Best Regards,

    Icey

     

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

4 Replies