Identify consecutive dates in the same column with regard to respective IDs

[rnd2789]

Greetings everyone. I have recently started using PBI and have been stuck with identifying consecutive dates problem for days. Please suggest something if any solutions or ideas.

I have a table with columns (example dataset Mock_PBI (1).xlsx) - date of Purchase, Product ID, Product Price, and Product Number.

I am working on finding consecutive dates (+ 1 day and -1 day) for each Product ID and then summing up the product price for those dates. I have tried using the next day function for identifying +/- 1 day in a column but it only works correctly for the dataset with a single Product ID and not for multiple IDs.  

For instance, this is the table I have :

And This is the table I wish to achieve with a new column (Final Price) with calculated prices based on the consecutive dates for each product ID:

Please note that I have a very big dataset and the dataset here is a made-up small example set. 

it would be a great help if there are any suggestions for the problem.

Thank You

[Anonymous]

Hi @rnd2789 ,

If you have a table with three consecutive days of data, then this problem becomes very simple. As shown in the figure below:

Measure = CALCULATE(SUM(Sheet1[Product price]),FILTER(ALL(Sheet1),'Sheet1'[product ID]=SELECTEDVALUE(Sheet1[product ID])))

In your example data, only when the previous day's data values are available, you may need to consolidate the data for better calculations.

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[rnd2789]

Hi, Thanks for the solution. 

However, the problem when taking this approach is that, For a product ID, all the prices would be summed up.

The end goal is to sum only the prices with consecutive dates within a product ID. Anything you can suggest for the problem? 

Also I have made a mistake while uploading the data, actually there are only three product ID's. corrected data Mock_PBI (1).xlsx