Forum Discussion

rnd2789's avatar
rnd2789
New Member
4 years ago

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

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
rnd2789
New Member
4 years ago

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