Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
KiKa
New Member

Calculate days between order date of order n and order n+1 of each customers in Power BI

How can I calculate the number of days between order n and order n+1 for each customer in Power BI?

I want to show and analyze the timespan*(in the day)* between first and second, second and third, etc orders.

 

222.JPG

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

Hi @KiKa ,

You can create a calculated column as below to get it:

Number of days = 
VAR _nextodate =
    CALCULATE (
        MIN ( 'Table'[order date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[user id] = EARLIER ( 'Table'[user id] )
                && 'Table'[order id] > EARLIER ( 'Table'[order id] )
        )
    )
RETURN  
    DATEDIFF ( 'Table'[order date], _nextodate, DAY )

yingyinr_0-1661239920848.png

Best Regards

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

View solution in original post

3 REPLIES 3
v-yiruan-msft
Community Support
Community Support

Hi @KiKa ,

You can create a calculated column as below to get it:

Number of days = 
VAR _nextodate =
    CALCULATE (
        MIN ( 'Table'[order date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[user id] = EARLIER ( 'Table'[user id] )
                && 'Table'[order id] > EARLIER ( 'Table'[order id] )
        )
    )
RETURN  
    DATEDIFF ( 'Table'[order date], _nextodate, DAY )

yingyinr_0-1661239920848.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@KiKa , Try a new column like

 

New column =
var _max = maxx(filter(Table, [User Id] =earlier([User Id]) && [Order Id] < earlier([Order Id]) ),[Order ID])
return
datediff([order Date] , maxx(filter(Table, [User Id] =earlier([User Id]) && [Order Id]= _max ),[Order Date]) , Day)+1

@amitchandak 

 

Hey! I used your proposed solution above to solve a similar problem. However, the formula seems to struggle whenever the second order is in a new year or whenever a customer has placed two orders at the same day (see below example). In those cases it turns out negative.

 

ErikOmni_0-1665077209715.png

 

ErikOmni_2-1665077566667.png

 

ErikOmni_3-1665077662539.png

this is how the formula looks like with my data

 

Days between orders =
var _max = maxx(filter(OrderData, [CustomerID] =earlier([CustomerID]) && [OrderID] < earlier([OrderID]) ),[OrderID])
return
datediff([OrderDate] , maxx(filter(OrderData, [CustomerID] =earlier([CustomerID]) && [OrderID]= _max ),[OrderDate]) , Day)+1


Any ideas? Thanks!
 
 

 

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.