Sumx and datesbetween two date columns

[AlexanderBP]

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

[richbenmintz · Accepted solution]

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 · Accepted solution]

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.

[richbenmintz · Accepted solution]

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!

[Greg_Deckler]

@AlexanderBP - I'm not sure I understand what you are trying to do with your DATESBETWEEN statement. Can you explain what you are trying to do in your formula?

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[amitchandak]

@AlexanderBP , a similar discussion on going on

https://community.powerbi.com/t5/DAX-Commands-and-Tips/how-to-count-customers-transactions-that-defi...

Check the suggested solutions there