Compare rows and sum value

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