Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Length Between Purchases

Hi Experts,

 

I am trying to calculate the length between all customers first and second purchase. Here is a measure I have:

 

VAR temptable =
ADDCOLUMNS (
VALUES ( 'Table'[date] ),
"RANK", RANKX (
VALUES ( 'Table'[date] ),
'Table'[date],
,
DESC,
DENSE
)
)
RETURN 
DATEDIFF (
MINX ( FILTER ( temptable, [RANK] = 2 ), 'Table'[date] ),
MINX ( FILTER ( temptable, [RANK] = 1 ),'Table'[date]),
DAY
) )
 
This works when put in a table with all customer id's and evaluates each customer indivdually.
 
Does anyone know how I can write a measure that will calculate a single figure which sums the length for all customers.
 
I.e. customer 1 has a length of 14 days and customer 2 has a length of 1 day. The measure will then display a figure of 15. 
 
Thanks in advance!
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    I suggest you to create a new measure based on the original measure you created before.

     

    Diff with correct total = SUMX(VALUES('Table'[Customer ID]),[Basic])

     

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

3 Replies

  • rajulshah's avatar
    rajulshah
    Resident Rockstar
    4 years ago

    Hello Anonymous ,

     

    Can you please share your sample data structure?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Fact table looks like this:

       

      Date is measure above refers to purchase date.

       

      I hope that helps, cannot share the file for confidential reasons

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

         

        I suggest you to create a new measure based on the original measure you created before.

         

        Diff with correct total = SUMX(VALUES('Table'[Customer ID]),[Basic])

         

        Result is as below.

         

        Best Regards,
        Rico Zhou

         

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