Sum Start End Time Column

[Anonymous]

Hi there!

I have two tables and would like to sum between the start and end times.

My data is hourly like so:

Table1

Date	Value
7/18/18 7:00 PM	1
7/18/18 8:00 PM	1
7/18/18 9:00 PM	1
7/18/18 10:00 PM	1
7/19/18 7:00 AM	2
7/19/18 8:00 AM	2
7/19/18 9:00 AM	2
7/19/18 10:00 AM	2
7/19/18 7:00 PM	3
7/19/18 8:00 PM	3
7/19/18 9:00 PM	3
7/19/18 10:00 PM	3

and I would like to sum as so:

Table2

Start	End	Sum
7/18/2018 7:00:00 PM	7/19/2018 7:00:00 AM	4
7/19/2018 7:00:00 AM	7/19/2018 7:00:00 PM	8
7/19/2018 7:00:00 PM	7/20/2018 7:00:00 AM	12

They are related by Start - Date.

Any thoughts?

Thanks!

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may use DAX below to add a calculated column.

Column =
SUMX (
    FILTER ( Table1, Table1[Date] >= Table2[Start] && Table1[Date] < Table2[End] ),
    Table1[Value]
)

[Anonymous]

This worked perfectly, thank you. Can you please provide it as a measure too?