I was helping a user on http://community.powerbi.com this week. The user had a list of service desk tickets for multiple customers and the requirement was to identify when a customer opened a second new ticket within 7 days of closing a previous ticket. This is not an uncommon scenario so I thought it would make a good blog article.
One of the challenges that new users have when using Power BI is to decide if they should use Power Pivot (DAX modelling) or Power Query (PQL shaping) to solve each problem. And if the answer is DAX, then they also need to decide if it should be a measure or calculated column. There is no one single correct answer to these questions and it always depends on the circumstances. I generally recommend to shape your data properly prior to loading to Power Pivot, that way the DAX formulas are easier to write and the reporting database will be more performant. I looked at the options for this problem and decided to use Power Query to shape the data before loading. Power Pivot and DAX are not really designed to do ordinal row comparisons, so this task is better handled with Power Query.
For this particular problem I thought I would produce a video that demonstrates one way in which this problem can be solved. Here it is.
Learn to be a Power Query Expert
If you want to fast track your learning so you can leverage all the power of Power Query, take a look at my self-paced online Power Query training course. You can also watch some free sample Power Query training videos here to get a feel about how you are going to learn.
This training has over 7 hours of video explaining concepts like this. Once you know how, you will be able to save time and do fantastic things you never dreamed possible as a Power Query expert.