Showing Sum of Data Between Two Dates

[ShaneL79]

Hello Everyone,

I have a very large weekly report (created by someone else). I am having trouble with one function. 

One chart shows incorrect values because it only accounts for the start date, not the end date. This means that if an event crosses into a second (or more) week, it only records the time in the present week.

I have went through the data, thanks to a user of this site, and separated each event out by daily totals into a new table. Now my data looks like this:

DATA TABLE:

EVENT ID	START DATE	END DATE	DURATION
01	11/26/2022 2:00:00 PM	11/26/2022 11:59:59 PM	9.999
01	11/27/2022 12:00:00 AM	11/27/2022 8:00:00 AM	8
02	11/27/2022 4:00:00 PM	11/27/2022 5:00:00 PM	1

There is also a table for "Weeks" that is used by the drop down splicer. It has the following fields:

WEEK TABLE:

START OF WEEK	END OF WEEK	DROPDOWN	REPORT NAME
Sunday, November 27, 2022	Saturday, December 3, 2022	2022-12-03	Nov 27, 2022 - Dec 3, 2022
Sunday, November 20, 2022	Saturday, November 26, 2022	2022-11-27	Nov 20, 2022 - Nov 26, 2022

Every chart in this large report uses that table for dropdown list. There is another data table that has been getting used that looks like this:

EVENT TABLE:

EVENT ID	RECEIVED TIME	START TIME	END TIME	DURATION
01	11/26/2022 2:00:00 PM	11/26/2022 2:00:00 PM	11/27/2022 8:00:00 AM	18
02	11/27/2022 4:00:00 PM	11/27/2022 4:00:00 PM	11/27/2022 5:00:00 PM	1

This is the original table where an entire event is on a single row. In contrast, the DATA TABLE above splits events into multiple rows when they go across multiple days.

Not all events have a "START TIME" or "END TIME" so those can be null in both EVENT and DATA tables. To try to clarify that, received time is the time an alert rings in. START TIME and END TIME are only used when workers are sent to repair the issue. It is "null" otherwise.

What I Want To Do:

I want to create a chart that looks at the DATA TABLE and only includes values with a START DATE between the WEEK TABLE START/END dates. Using Event ID #1, Only row #1 (Duration = 9.999) of the DATA TABLE would show when week Nov 20, 2022 - Nov 26, 2022 was selected, and row 2 and event #2 would show when week Nov 27, 2022 - Dec 3, 2022 was selected. 

This seems like it is something easy, but I cannot get it so I assume multiple connections between the tables are involved and likely need to either trace it down OR rebuild the chart using completely different data (aside from the drop down which cannot be changed).

[DataInsights · Accepted solution]

@ShaneL79,

Try this measure:

Duration = 
VAR vWeekStart =
    SELECTEDVALUE ( WeekTable[START OF WEEK] )
VAR vWeekEnd =
    SELECTEDVALUE ( WeekTable[END OF WEEK] )
VAR vResult =
    CALCULATE (
        SUM ( 'DataTable'[DURATION] ),
        INT ( 'DataTable'[START DATE] ) >= vWeekStart, // use INT to remove time portion
        INT ( 'DataTable'[START DATE] ) <= vWeekEnd
    )
RETURN
    vResult

No relationship exists between WeekTable and DataTable.

[ShaneL79]

Worked perfectly. Thank you very much!