Get Time Encapsulated by Date Ranges

[aakunkle]

I've got a table with a bunch of rows with columns for MachineID(int), StartTime(DateTime), and EndTime(DateTime).  I want to get the total amount of time falling inside at least one of the time ranges for each entry, for a given MachineID.

Is there a way to create a Measure (mapping to MachineID) that does this?  I can think of a way to do this with code, but searching through DAX functions doesn't seem to be returning anything useful.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[aakunkle]

So for

MachineID	StartTime	EndTime
1	1/3/2021 0:00:00	1/9/2021 0:00:00
1	1/3/2021 0:00:00	1/15/2021 0:00:00
1	3/4/2021 0:00:00	3/11/2021 0:00:00
1	3/6/2021 0:00:00	3/7/2021 0:00:00
1	3/10/2021 0:00:00	4/18/2021 0:00:00
2	1/3/2021 0:00:00	1/7/2021 0:00:00
2	2/5/2021 0:00:00	2/20/2021 0:00:00

the output would be

MachineID	TimeEncapsulated(days)
1	57
2	19

[Ashish_Mathur]

How did you arrive at 57 and 19?  Explain.

[aakunkle]

The 57 comes from the total days covered by the date ranges covering 1/3 to 1/15 and 3/4 to 4/18 for machine 1.  The 19 comes from total days covered by the two non-overlapping date ranges for machine 2.

[Ashish_Mathur · Accepted solution]

Hi,

If you include the beginning and ending dates, the results should be 59 and 21.  You may download my PBI file from here.

Hope this helps.

[aakunkle]

Your PBI file doesn't indicate how "Date" was generated from "StartTime" and "EndTime".  Did you generate a new table by adding an entry for every day that fell within the range for a given machine ID, or something else?

[Ashish_Mathur]

Hi,

Please study the steps in the Query Editor.