Compare rows and sum value

[blisbao]

Hello to all,

I'm using Power BI to analyse a very large database and I'm stuck in a situation.

I have a table with time periods and amounts for each item. Each row has a start and stop time.

I need to summarize all the continuous time periods. For each name, I need to combine rows where the stop time is equal the next start time (continuous work) and sum their amount column.

SOURCE DATA:

Name	Start	Stop	Amount
A	14/07/2019 10:02:31	14/07/2019 10:05:20	95
A	17/07/2019 07:05:55	17/07/2019 07:08:08	70
A	17/07/2019 07:18:43	17/07/2019 07:33:43	862
A	17/07/2019 07:33:43	17/07/2019 07:36:30	137
A	17/07/2019 07:37:34	17/07/2019 07:39:53	108
B	15/07/2019 07:30:13	15/07/2019 07:31:12	14
B	15/07/2019 07:32:37	15/07/2019 07:47:37	900
B	15/07/2019 07:47:37	15/07/2019 08:02:37	900
B	15/07/2019 08:02:37	15/07/2019 08:17:37	849
B	15/07/2019 08:17:37	15/07/2019 08:20:38	150
B	15/07/2019 08:21:56	15/07/2019 08:23:21	9
B	15/07/2019 08:23:30	15/07/2019 08:38:30	899

DESIRED RESULT:

Name	Start	Stop	Amount
A	14/07/2019 10:02:31	14/07/2019 10:05:20	95
A	17/07/2019 07:05:55	17/07/2019 07:08:08	70
A	17/07/2019 07:18:43	17/07/2019 07:36:30	999
A	17/07/2019 07:37:34	17/07/2019 07:39:53	108
B	15/07/2019 07:30:13	15/07/2019 07:31:12	14
B	15/07/2019 07:32:37	15/07/2019 08:20:38	2799
B	15/07/2019 08:21:56	15/07/2019 08:23:21	9
B	15/07/2019 08:23:30	15/07/2019 08:38:30	899

Thanks in advance.

[lc_finance · Accepted solution]

Hi @blisbao ,

You can download my proposed solution from here.

Here is how I would approach it:

1) add an Index column. This allows you to clearly identify each row. You can do it in Power Query Editor by going to Add Column -> Index Column

2) Add a calculated column to identify the Real Start. Only rows that are the first in a sequence have a ' Real Start'.

Here is the DAX formula:

Real Start = 

VAR currentStart = [Start]

VAR isRealStart = COUNTX(
    FILTER('Transactions',
    'Transactions'[Stop]=currentStart)
, [Stop]) = BLANK()


RETURN IF(isRealStart, currentStart, BLANK())

Here is the result:

3) Add a column to identify the index of the row with the real start.

Here is the DAX formula:

Real Start Index = 

VAR isRealStart = NOT [Real Start]= BLANK()
VAR currentIndex = [Index]

VAR realStartIndex = MAXX(
    FILTER('Transactions', AND( [Index]<=currentIndex, NOT [Real Start] = BLANK()))
, [Index])

RETURN realStartIndex

and here is the result:

Now you can repeat the same for the Stop.

Finally, add a calculated column with the sum of the amounts only if the row is the 'Real Start' of the sequence. You can find the formulas for these additional measures in the solution Power BI file.

To obtain exactly your 'desired result' table, you filter out empty rows in the column ' Real Start' .

Does this help you? Do not hesitate if you have further questions.

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com