Compare Rows and sum

[JWE]

Hi folks

I need help for this scenario.

For 4 inbound deliveries (from the same vendor) I have 4 rows with date and time.

When I calculate the time in minutes (Minuten je Beleg) and sum the column I get the complete sum (660.46 min) of each inbound.

Problem: I only want the (start-end) sum like marked in red.

Means: The duration of all inbounds for one vendor at one day e.g. ~262 minutes.

And to make it more challenging - only when there is no gap between the times, means when there is an overlap of all times.

Any idea - thanks Jorg

[JWE]

Another note:

How can I recognize and conisder the gap in the times from different documents.

Another example like before:

When I select the date (06.01.2016) I get 4 douments.

In this case I have one (LagerNr B213121) without overlapping time with the other 3.

This ends before the new starttime of the next document (higlighted in yellow).

So the result (sum of time) is not what I need.

In these special cases I want the sum of the two different results (8,52 + 178,17=186,69).

How to realised that?

Thanks in advanced Jorg

[JWE]

Hi Yuliana

thanks for help.

When I use your DAX (syntax see below) I get the result in picture 1.

Only when I select the items in the slicer (picture 2) I get the correct result.

It should also work without the selection - is it possible?

Yuliana =
DATEDIFF (
    CALCULATE (
        MIN ( 'Lagerbeleg'[Start DatumUhrzeit] );
        ALLEXCEPT ( 'Kreditor'; 'Kreditor'[KreditorNr] )
    );
    CALCULATE (
        MAX ( 'Lagerbeleg'[Ende DatumUhrzeit] );
        ALLEXCEPT ( 'Kreditor'; 'Kreditor'[KreditorNr] )
    );
    SECOND)
    / 60

[v-yulgu-msft]

Hi @JWE,

Please refer to below measure;

Sum Between Start and End =
DATEDIFF (
    CALCULATE (
        MIN ( 'Compare Rows and sum'[Start] ),
        ALLEXCEPT ( 'Compare Rows and sum', 'Compare Rows and sum'[KreditorNr] )
    ),
    CALCULATE (
        MAX ( 'Compare Rows and sum'[End] ),
        ALLEXCEPT ( 'Compare Rows and sum', 'Compare Rows and sum'[KreditorNr] )
    ),
    SECOND
)
    / 60

Result:

If I have something misunderstood, please correct me and describe your requirment more clearly.

Thanks,
Yuliana Gu