Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Aggregate data based on condition to be computed on raw data

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

ItemTimestampStartEndDuration
AAA22/05/2021 10:0099564h
AAA22/05/2021 14:0054322h
AAA23/05/2021 01:003271h
AAA24/05/2021 09:0095175h
AAA25/05/2021 11:0083453h
AAA25/05/2021 13:004463h
AAA25/05/2021 17:0091384h
AAA25/05/2021 18:0030141h
BBB22/05/2021 18:0075452h
BBB23/05/2021 08:0093234h
BBB26/05/2021 12:002241h
BBB27/05/2021 12:0094283h
CCC19/05/2021 12:0096582h
CCC20/05/2021 10:0058102h
CCC20/05/2021 13:00541h
CCC24/05/2021 12:0096582h
CCC25/05/2021 10:0058780h
CCC25/05/2021 13:0074123h

 

OUTPUT

ItemTimestampStartEndDuration
AAA22/05/2021 10:009977h
AAA25/05/2021 17:0091145h
AAA24/05/2021 09:0095175h
BBB23/05/2021 08:009345h
CCC19/05/2021 12:009645h

 

7 Replies

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

    • Anonymous's avatar
      Anonymous
      Not applicable

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

    • Anonymous's avatar
      Anonymous
      Not applicable

      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!