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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jadhalaoui
Helper I
Helper I

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

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.

 

jadhalaoui_0-1604408227151.png

 

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. 

 

jadhalaoui_1-1604408629181.png

 

Thank you for your help

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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)

View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

@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)

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

 

jadhalaoui_0-1604412181561.png

 

 

Thanks

@amitchandak  - Thank you for your response.

 

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

 

jadhalaoui_0-1604410275732.png

 

@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
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat 

 

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

 

Thanks

 

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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