Powerbi create mesaure from multiple tables

Question

I want to create a measure where i get the ammount of written hours in a shift. The issue is that my DAX knowledge is not sufficient to create such a formula.

The tables are as follows:

TABLE 1 (Shift information)

END      |  START   | EMPLOYEE
datetime | datetime | varchar

TABLE 2 (Written time)

END      |  START   | DURATION | EMPLOYEE
datetime | datetime | duration | varchar

Now i have created an simular SQL to create this data, unfortunately the SQL is so intensive that it would take too long to run it.

The formula in the SQL is as follows:

SUM(table2.duration) WHERE table2.end BETWEEN table1.start and table1.end

Any help would be appreciated!

James Hamilton James Hamilton · Accepted Answer · 2018-11-25T20:26:45

Try DATESBETWEEN

=
CALCULATE (
    SUM ( table2[duration] ),
    DATESBETWEEN (
        table2[end],
        LASTDATE ( table1[start] ),
        LASTDATE ( table1[end] )
    )
)

Powerbi create mesaure from multiple tables

1 Answers