cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
redmeg74 Frequent Visitor
Frequent Visitor

Re: Calculate Days between OrderDate per Customer where Order date is in the same Column???

You beauty Matt!  I've just spent two hours trying to crack that - so happy I found your post!

Re: Calculate Days between OrderDate per Customer where Order date is in the same Column???

Hi Matt,

Sorry I'm lat to the party, but I noticed tht you mentioned:

 

Power Pivot is not really built to handle relative comparison between rows in a table. 

 

However such requirement is pretty much everything I'm doing with a data dump (e.g. find difference between two rows based on same value of some column). Does it mean that I should re-format the raw dump to better suit Power Pivot?

 

Thanks in advance!

Highlighted
Super User
Super User

Re: Calculate Days between OrderDate per Customer where Order date is in the same Column???

Possibly, it is hard to say without seeing the details.  Generally the order of rows in a DB are not important.  If you need to compare change in status between rows (for example), then you can do it in DAX and or restructure the data.  Both approaches have their strengths and weaknesses.  It depends.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Re: Calculate Days between OrderDate per Customer where Order date is in the same Column???

Thanks Matt, I found it particularly to do anything for row operations without a convoluted formula and I'm wondering if I'm choosing the wrong tool.

 

For example:

I have the following table:

 

Transaction ID | Event Type | IP Address | Datetime | Email Address |

 

I have a bunch of these transactions, and an [Event Type] could be a Purchase (transfer real money for virtual coins), or a Virtual Purchase (transfer virtual coins for web contents). Now for each Virtual Purchase I'm missing the email address, so I would like to set up a Calculated Column, say named [Possible Email Address] for each row that has [Event Type] = "Virtual Purchase", with the following rule:

 

Search all previous rows, find a row that has a matching IP Address, and use the [Email Address] of that row to populate [Possible Email Address] of this row.

 

Example:

Transaction ID | Event Type | IP Address | Datetime | Email Address |

100001 | Purchase | 1.1.1.1 | 2018-01-11 | abc@hotmail.com

100002 | Purchase | 1.4.5.6 | 2018-01-11 | cdfg@gmail.com

...(after many rows)

100356 | Virtual Purchase | 1.1.1.1 | 2018-01-13 | UNKNOWN

 

So after adding the calculated column Transaction ID 100356 should become this:

 

100356 | Virtual Purchase | 1.1.1.1 | 2018-01-13 | abc@hotmail.com