Adding a Calculated Column (Date difference) with Values Based On Multiple Rows

[jadhalaoui]

Hello!

Objective

Create a new (5th) column that has the date difference (in rounded up days) between values in two rows for data corresponding to unique customers.

The Data

Each Order ID corresponds to a a Customer ID. Order IDs are unique to each order. Customer IDs are unique to each customer. Each Customer can have several Order IDs assigned to them.

The Unique Customer Sequence (column 3) is a calculated column using Power Query that determines the sequence of a customer's specific order, and is sorted by time. 

The pickup_requested column correspond to the data and time the pickup was requested, and is what determines the order of the Unique Customer Sequence for each Customer ID.

Approaches Tried

I tried creating a 5th calculated column (pickup_requested_previous) which takes the pickup_requested date of a previous sequence. So, for sequence 2, it would take the pickup_date for sequence 1, and so on. For Unique Customer Sequence 4, the 5th column will have the pickup_requested date for Sequence 3 - and so on.

DATEDIFF(CALCULATE(MAX(DXB_DP[pickup_requested]),FILTER(DXB_DP,DXB_DP[customer_id]=EARLIER(DXB_DP[customer_id])&&DXB_DP[Unique Customer Sequence]=EARLIER(DXB_DP[Unique Customer Sequence])-1)),DXB_DP[pickup_requested],MINUTE)/60*24

I am however getting a Token Literal error. 

Thank you for your help

[amitchandak · Accepted solution]

@jadhalaoui , Not very clear. Try a new column in DAX

Column = var _1 = maxx(FILTER('Table','Table'[Name]= EARLIER('Table'[Name]) && 'Table'[unique customer sequence] < EARLIER('Table'[unique customer sequence])),LASTNONBLANKVALUE('Table'[unique customer sequence],'Table'[pickup_requested])) return datediff(_1,[pickup_requested],hour)

[jadhalaoui]

Hello @amitchandak 

Thank you - your DAX got me close to what I need. I am getting however the last Unique Sequence ID to appear in the column instead of the last Pickup_requested_date

Have researched MAXX and LASTNONBLANKVALUE - but cannot get the equation to return what I need despite manipulating the LASTNONBLANKVALUE expression

Thanks

[jadhalaoui]

@amitchandak  - Thank you for your response.

From Transform Data > Add Column > Custom Column - I tried the following and got the error Token EoF Missing

@mahoneypat 

Thanks - found out how to do it. 

Getting an error about comparing values of type Number and values of type Text. Debugging as we speak.

I need to get a better command of the functions and syntax. Will be looking into it

[mahoneypat]

It looks like you are trying to put a DAX expression in a query custom column, which is incorrect.  Load your table, and then hit the New Column on the ribbon to enter your DAX column.

Regards,

Pat

[jadhalaoui]

@mahoneypat 

Sorry if this is an amateur question - but how do I load the table the way you have just mentioned it?

Thanks

[mahoneypat]

Here is a link that will help.  Your expression looks valid (I put it in DAXformatter.com to check), but you'll have to see if it gets your desired result.  You will need to add a column name when you enter it.  New Column = <your expression>

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-tutorial-create-calculated-columns

Regards,

Pat