Forum Discussion

cplesner's avatar
cplesner
Helper III
9 years ago
Solved

Average Days between Orders

 

Hi

I'm trying to make a measure that shows the average days between orders from a specific Customer plus another measure that shows the average days between the orders of a specific Product from each Customer.

 

Below is the example where i have typed in the days manually, which is basically just a datediff.

Ex. Cust A order Product A for the first time on 01-01-2016. 60 Days later the same customer orders again, while the same Customer orders the same product 83 later.

 

Eventuelly the output should show that for example

Cust A is ordering Product A on average every 83 Days (2 Orders)

Cust A is ordering Product B on average every 32,5 Days (3 Order)

Cust A is ordering on average 31,25 Days (5 Order)

 

I can get these to the number by calculating the differences in SQL, but i would like to keep it in DAX

 

Thanks in advance

Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi cplesner,

     

    You an also create calculated columns to calculate DateDiff values based for specific customer, or both customer and product. See:

     

    Previous-Customer = CALCULATE(MAX(Table1[Date]), (FILTER(Table1, EARLIER(Table1[Customer])=Table1[Customer] && EARLIER(Table1[Date])>Table1[Date])))

    DateDiff-C = DATEDIFF('Table1'[Previous-Customer],'Table1'[Date],DAY)

     

    Previous-PC = CALCULATE(MAX(Table1[Date]), (FILTER(Table1, EARLIER(Table1[Customer])=Table1[Customer] && EARLIER(Table1[Product])=Table1[Product] && EARLIER(Table1[Date])>Table1[Date])))

    DateDiff-PC = DATEDIFF('Table1'[Previous-PC],'Table1'[Date],DAY)

     

     

    Best Regards,
    Qiuyun Yu

5 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    Hi cplesner,

     

    In your scenario, you can create two measures like below:

     

    DateDiff-ProductCustomer = VAR currentday = MIN( Table1[Date] )
RETURN
( IF(
	ISERROR( 
DATEDIFF(  currentday,
CALCULATE( MAX( Table1[Date] ), FILTER( ALLEXCEPT(Table1, Table1[Product], Table1[Customer]), Table1[Date] < MAX( Table1[Date] ) )),DAY )),

DATEDIFF(  CALCULATE( MAX( Table1[Date] ), FILTER( ALLEXCEPT(Table1, Table1[Product], Table1[Customer]), Table1[Date] < MAX( Table1[Date] ) )),currentday,DAY) )
)

     

    DateDiff-Customer = 
VAR currentday = MIN( Table1[Date] )
RETURN
( IF(
	ISERROR( 
DATEDIFF(  currentday,
CALCULATE( MAX( Table1[Date] ), FILTER( ALLEXCEPT(Table1, Table1[Customer]), Table1[Date] < MAX( Table1[Date] ) )),DAY )),

DATEDIFF(  CALCULATE( MAX( Table1[Date] ), FILTER( ALLEXCEPT(Table1, Table1[Customer]), Table1[Date] < MAX( Table1[Date] ) )),currentday,DAY) )
)

     

     

    Best Regards,
    Qiuyun Yu

    • cplesner's avatar
      cplesner
      Helper III
      9 years ago

      Hi Qiuyun

      Thanks for the answer, it works fine if you actively use the date in the pivot. However I would prefer to have it as a calculated column/calculated table so that i can do an average measure on it and i don't have to actively use the Date attribute.

       

      I have solved it in SQL creating another table like below using Partition by and Rank, but DAX would be preffered

       

      WITH Ranked as (

      SELECT a.[Product],a.[Customer],a.[Date],

      rank() over (PARTITION BY a.[Product],a.[Customer] order by a.[Date]) as rankid,

      a.Amount

      FROM [Table1] a

      )

      select

      r1.[Product],

      r1.[Customer],

      r1.[Date],

      r1.rankid,

      datediff(d,r2.[Date],r1.[Date]) as DaysBetween,

      1 as InvCounter

      FROM Ranked r1 LEFT OUTER JOIN Ranked r2 ON

      r1.[Product]=r2.[Product] and r1.[Customer]=r2.[Customer] and

      (r1.rankid=r2.rankid+1)

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Community Support
        9 years ago

        Hi cplesner,

         

        You an also create calculated columns to calculate DateDiff values based for specific customer, or both customer and product. See:

         

        Previous-Customer = CALCULATE(MAX(Table1[Date]), (FILTER(Table1, EARLIER(Table1[Customer])=Table1[Customer] && EARLIER(Table1[Date])>Table1[Date])))

        DateDiff-C = DATEDIFF('Table1'[Previous-Customer],'Table1'[Date],DAY)

         

        Previous-PC = CALCULATE(MAX(Table1[Date]), (FILTER(Table1, EARLIER(Table1[Customer])=Table1[Customer] && EARLIER(Table1[Product])=Table1[Product] && EARLIER(Table1[Date])>Table1[Date])))

        DateDiff-PC = DATEDIFF('Table1'[Previous-PC],'Table1'[Date],DAY)

         

         

        Best Regards,
        Qiuyun Yu