# Time overlap in PowerPivot



## karlden (May 30, 2016)

Hi

I got a table in PowerPivot with start and end date and time for some productionlines. I want to do an analasys of overlaps.
How can I in calculate if there is an overlap in time and how long that overlap is per day? 

So if I have an calender table with each dates, how many overlapping hours do I have each day?

Line	        start	                                end
Line 1	2014-06-02 05:36:00 	2014-06-02 23:56:58
Line 1	2014-06-03 05:26:31 	2014-06-04 00:00:59
Line 1	2014-06-04 04:54:44 	2014-06-05 00:00:01
Line 2	2014-06-04 14:45:33 	2014-06-04 21:44:24
Line 2	2014-06-05 05:32:12 	2014-06-06 00:10:00
Line 1	2014-06-09 05:42:00 	2014-06-10 00:10:00
Line 1	2014-06-10 05:39:16 	2014-06-11 00:10:00
Line 2	2014-06-10 14:45:00 	2014-06-10 22:43:54
Line 2	2014-06-11 04:53:13 	2014-06-11 12:38:47
Line 1	2014-06-11 13:03:00 	2014-06-11 23:54:13


----------



## Ozeroth (Jun 1, 2016)

Hi karlden,

This is an interesting one. It's a bit like 'events in progress', but measuring simultaneous events.

*A question that needs to be answered to define the overlaps measure:
*If 3 Lines are operating simultaneously for an hour, what is the value of the overlap measure?

*Option 1: Overlaps = (# Lines - 1) * Time = (3 - 1)*1 =2
*Two Lines are running for 1 hour on top of the first line, => 2 overlaps for an hour.
*Option 2: Overlaps = MIN ( ( # Lines - 1),1) * Time = (2 - 1)*1 =1
*S_ome_ Lines are running on top of the first line for 1 hour => 1 overlap for an hour
*Option 3: Overlaps = # of distinct pairs of lines * Time = (3 choose 2)*1=3
*Three pairs of lines (1/2, 1/3, 2/3) operate for 1 hour => 3 overlaps for an hour

*Options 1 & 2* are hard unless you discretize time, down to minute/hour for example. Then you can use a variant of an events in progress measure that sums events in progress by the minute/hour.

*Option 3 *is possible with continuous datetime, by iterating over each pair of lines and measuring overlaps. And if you only ever care about overlaps between two lines (i.e. always select two lines at a time) then this is definitely fine.

In any case, here's an example of a measure for Option 3 (with the Old DAX and the New). It's one of those formulas that constructs a table with ADDCOLUMNS/FILTER/GENERATE/SUMMARIZE then sums with SUMX.

Not saying this is optimal or anything, but demonstrates how this can be handled 
Example file in Excel 2010 (let me know if you want a different version):
https://www.dropbox.com/s/hdd679u87upra8i/Overlapping datetime.xlsx?dl=0

*Assumptions*


Fact table is called Schedule, with columns Line, start, end (and possibly other columns)
Date table is called 'Date' with column Date

*Old DAX (Excel 2010/2013)
*

```
=
SUMX (
    VALUES ( 'Date'[Date] ),
    SUMX (
        ADDCOLUMNS (
            FILTER (
                GENERATE (
                    ADDCOLUMNS (
                        GENERATE (
                            Schedule,
                            SUMMARIZE (
                                ADDCOLUMNS (
                                    CALCULATETABLE (
                                        Schedule,
                                        ALL ( Schedule ),
                                        ALLSELECTED ( Schedule[Line] ),
                                        Schedule[start] <= EARLIER ( Schedule[end] ),
                                        Schedule[end] >= EARLIER ( Schedule[start] ),
                                        Schedule[Line] > EARLIER ( Schedule[Line] )
                                    ),
                                    "Line 2", Schedule[Line],
                                    "start 2", Schedule[start],
                                    "end 2", Schedule[end]
                                ),
                                [Line 2],
                                [start 2],
                                [end 2]
                            )
                        ),
                        "Overlap start", IF ( Schedule[start] >= [start 2], Schedule[start], [start 2] ),
                        "Overlap end", IF ( Schedule[end] <= [end 2], Schedule[end], [end 2] )
                    ),
                    DATESBETWEEN ( 'Date'[Date], [Overlap Start], [Overlap End] )
                ),
                'Date'[Date] = EARLIER ( 'Date'[Date] )
            ),
            "Overlap hours", 24
                * (
                    IF ( [Overlap end] < 'Date'[Date] + 1, [Overlap end], 'Date'[Date] + 1 )
                        - IF ( [Overlap start] >= 'Date'[Date], [Overlap start], 'Date'[Date] )
                )
        ),
        [Overlap hours]
    )
)
```


*New DAX (Excel 2016 or Power BI, can use MAX/MIN with scalars and SELECTCOLUMNS)
*

```
=
SUMX (
    VALUES ( 'Date'[Date] ),
    SUMX (
        ADDCOLUMNS (
            FILTER (
                GENERATE (
                    ADDCOLUMNS (
                        GENERATE (
                            Schedule,
                            CALCULATETABLE (
                                SELECTCOLUMNS (
                                    Schedule,
                                    "Line 2", Schedule[Line],
                                    "start 2", Schedule[start],
                                    "end 2", Schedule[end]
                                ),
                                ALL ( Schedule ),
                                ALLSELECTED ( Schedule[Line] ),
                                Schedule[start] <= EARLIER ( Schedule[end] ),
                                Schedule[end] >= EARLIER ( Schedule[start] ),
                                Schedule[Line] > EARLIER ( Schedule[Line] )
                            )
                        ),
                        "Overlap start", MAX ( Schedule[start], [start 2] ),
                        "Overlap end", MIN ( Schedule[end], [end 2] )
                    ),
                    DATESBETWEEN ( 'Date'[Date], [Overlap Start], [Overlap End] )
                ),
                'Date'[Date] = EARLIER ( 'Date'[Date] )
            ),
            "Overlap hours", 24
                * (
                    MIN ( [Overlap end], 'Date'[Date] + 1 )
                        - MAX ( [Overlap start], 'Date'[Date] )
                )
        ),
        [Overlap hours]
    )
)
```


----------



## ImkeF (Jun 2, 2016)

Almost sure that the Owens DAX-version is faster, but with M in Power Query it looks easier 


```
let
    Source = Schedule,
    AddIndex = Table.Buffer(Table.AddIndexColumn(Source, "Index", 0, 1)),
    #"Added Custom1" = Table.AddColumn(AddIndex, "Custom", each AddIndex),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Line", "start", "end", "Index"}, {"Line.1", "start.1", "end.1", "Index.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Custom", "Overlap", each if [Index]<>[Index.1] and [start.1]<[end] and [end.1] > [start] then Number.From(List.Min({[end], [end.1]})-List.Max({[start.1], [start]}))*24 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Overlap] <> 0))
in
    #"Filtered Rows"
```


----------



## karlden (Jun 2, 2016)

Hi, and thanks for the answers. I havent tested them yet.
But, to answer your Q Ozeroth, in this case I only want to compare two lines. One against another.


----------

