Aggregate data based on condition to be computed on raw data

[macas01]

Hello all,

I am pretty new on Power BI and i am working on a report where i need to aggregate data based on some specific conditions:

Here is an example of what i am trying to achive:

Conditions are

Aggregate/group on continous data (based on timestamp) with 

Start > 90 to End < 20 (taking the lowest End value)

If there are increment ignore (as last case shown on image)

Hope that is clear enough.

I have tryied different approach with DAX expression, Visuals, M Query, with descrete results but not exactly what i am looking for. I have tried to find something similar on this Forum but unsuccessfully, i start to think that maybe is not feasable as i want, and i need to change approach.

Do you have suggestions? 

Thanks!

Edit 3rd June: Adding sample data:

INPUT

Item	Timestamp	Start	End	Duration
AAA	22/05/2021 10:00	99	56	4h
AAA	22/05/2021 14:00	54	32	2h
AAA	23/05/2021 01:00	32	7	1h
AAA	24/05/2021 09:00	95	17	5h
AAA	25/05/2021 11:00	83	45	3h
AAA	25/05/2021 13:00	44	6	3h
AAA	25/05/2021 17:00	91	38	4h
AAA	25/05/2021 18:00	30	14	1h
BBB	22/05/2021 18:00	75	45	2h
BBB	23/05/2021 08:00	93	23	4h
BBB	26/05/2021 12:00	22	4	1h
BBB	27/05/2021 12:00	94	28	3h
CCC	19/05/2021 12:00	96	58	2h
CCC	20/05/2021 10:00	58	10	2h
CCC	20/05/2021 13:00	5	4	1h
CCC	24/05/2021 12:00	96	58	2h
CCC	25/05/2021 10:00	58	78	0h
CCC	25/05/2021 13:00	74	12	3h

OUTPUT

Item	Timestamp	Start	End	Duration
AAA	22/05/2021 10:00	99	7	7h
AAA	25/05/2021 17:00	91	14	5h
AAA	24/05/2021 09:00	95	17	5h
BBB	23/05/2021 08:00	93	4	5h
CCC	19/05/2021 12:00	96	4	5h

[ryan_mayu]

@macas01

I am confused with your logic

[macas01]

Hello ryan,

The previous row start from 95 and end at 17, while next one start from 83 that is greater of previous end (17).

Raw data are ordered by item and by timestamp. Ideally every "process" start from 100 and end at 0.

I would like to isolate and aggregate this "process" accordingly to reqs i wrote in my main post.

Hope that is more clear now.

Thanks!

[ryan_mayu · Accepted solution]

@macas01 

Here is a workaround for you, please see the attachment below

[macas01]

Thanks @ryan_mayu it does exactly what i needed! My only concern is regarding the computational load. My full data table size is about 30k/40k rows.

Do you think there could be problem?

Thanks!

[ryan_mayu]

@macas01 

I am not sure about that, you can have a try. If there is a performance issue, maybe you can ask for help on the community again to see if anyone else can provide better solution.

[lbendlin]

Please provide sample data in usable format (not as a picture) .

[macas01]

Thanks Ibendlin, i am going to add sample data in main post