Sumif equivalent in PowerBI doesn't work properly with DAX function

Question

I try to use SUMIF equivalent in PowerBI. I have read already a lot of similar topics describing the usage of CALCULATE and SUM combined with FILTER functions. But none of them could help me with my problem.

What I want to do:

A production line writes a time stamp into a CSV file when it starts an event (e.g. machine starts, machine stopps etc.). The CSV file is processed by PowerQuery in a way that all start dates are available in one column of a table. The column is called "Event Start".

To further analyze the events of the machine, I would like to add another column "Event End" that contains also the end date of each event. The end date is not provided by the CSV file but it is equal to the start date of the next event.

So what I need to do is to have a value in row n, column 2 that is taken from row n+1, column 1. In Excel, very easy to do so... One solution (not the best one - I know) is to use SUMIF together with an Index column and Index+1 column:

I came up with the idea to use SUMIF because I know that there is an equivalent function with DAX expression (CALCULATE, SUM and filters). Unfortunately in this case all the examples given in other threads don't work.

Could anybody help me with this? Maybe there is another DAX function available to solve this issue.

Marco Vos Marco Vos · Accepted Answer · 2019-07-04T20:40:19

Here are two options. The first one returns the [Start Event] where [Index] is 1 higher than [Index] in the current row.

The second one returns the smallest [Start Event] that is greater than the [Start Event] in the current row.

End Event =
CALCULATE (
    MAX ( 'Table1'[Start Event] ),
    FILTER ( 'Table1', 'Table1'[index] = EARLIER ( 'Table1'[index] ) + 1 )
)  

End Event 2 =
CALCULATE (
    MIN ( 'Table1'[Start Event] ),
    FILTER ( 'Table1', 'Table1'[Start Event] > EARLIER ( 'Table1'[Start Event] ) )
)

Sumif equivalent in PowerBI doesn't work properly with DAX function

1 Answers