DAX to calculate the amount of time between two datetime values with blackout periods

[tacamo]

I am trying to create a DAX for an added column in PowerBI to calculate the total amount of hours between two datetime values in table 1 and ignoring blackout periods identified in table 2 as status 0 periods by location. The Excel Array Formula is listed below.

Excel Array Formula: {=sum((Table2_Location.Range = Table1_Location.Value)*(Table2_Status.Range = 1)*(if(Table1_Stop.value<Table2_Stop.Range,Table1_Stop.Value,Table2_Stop.Range)-if(Table1_Start.Value>Table2_Start.Range,Table1_Start.Value,Table2_Start.Range)))*24}

Table 1

Location	Start	Stop	Calculated_hours
A	May 11, 2011 5:13:44 AM	Dec 19, 2011 6:43:49 PM	1780.7
B	Jul 11, 2011 10:03:34 PM	Jan 11, 2012 12:13:42 AM	3418.2

Table 2 (Blackout Periods, Status 0)

Location	Start	Stop	Status
A	Apr 11, 2011 12:00:00 AM	Jul 18, 2011 4:00:00 PM	0
A	Jul 18, 2011 4:00:00 PM	Sep 13, 2011 2:00:00 AM	1
A	Sep 13, 2011 2:00:00 AM	Dec 2, 2011 12:00:00 AM	0
A	Dec 2, 2011 12:00:00 AM	Jan 26, 2012 12:00:00 PM	1
B	Apr 18, 2011 12:00:00 AM	Jul 12, 2011 4:00:00 PM	0
B	Jul 12, 2011 4:00:00 PM	Sep 23, 2011 2:00:00 AM	1
B	Sep 23, 2011 2:00:00 AM	Nov 2, 2011 12:00:00 AM	0
B	Nov 2, 2011 12:00:00 AM	Jan 22, 2012 12:00:00 PM	1

The calculated hours for the Table1 records are 1780.7 and 3418.2 hours. Both records include a partial portion of the second respective non-blackout period.  The two tables are linked many-to-many by location.

Any assistance would be much appreciated.

Thank you.

Thomas Tucker

[v-cherch-msft]

Hi @tacamo 

Could you explain more about your Excel Array Formula?I cannot fully understand the logic.

Regards,

[tacamo]

Cherie Chen, 

Were you able to offer any guidance?

Thomas Tucker

[tacamo]

Cherie Chen,

The array formula, returns three equal item arrays, 1. A Boolean array for the records that match the location, 2. A Boolean array for the records that have a status of 1, and 3. The values array for each Table 2 record based on the if statement that calculates any partial periods.

The steps within the array formula are listed below.  The values are rounded for the example.

{=sum((Table2_Location.Range = Table1_Location.Value)*(Table2_Status.Range = 1)*(if(Table1_Stop.value<Table2_Stop.Range,Table1_Stop.Value,Table2_Stop.Range)-if(Table1_Start.Value>Table2_Start.Range,Table1_Start.Value,Table2_Start.Range)))*24}

{=sum((Table2_Location.Range = Table1_Location.Value)*(Table2_Status.Range = 1)*(if(Table1_Stop.value<Table2_Stop.Range,Table1_Stop.Value,Table2_Stop.Range)-if(Table1_Start.Value>Table2_Start.Range,Table1_Start.Value,Table2_Start.Range)))*24}

{=sum(({A;A;A;A;B;B;B;B}=A)*(Table2_Status.Range = 1)*(if(Table1_Stop.value<Table2_Stop.Range,Table1_Stop.Value,Table2_Stop.Range)-if(Table1_Start.Value>Table2_Start.Range,Table1_Start.Value,Table2_Start.Range)))*24}

{=sum(({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE})*(Table2_Status.Range = 1)*(if(Table1_Stop.value<Table2_Stop.Range,Table1_Stop.Value,Table2_Stop.Range)-if(Table1_Start.Value>Table2_Start.Range,Table1_Start.Value,Table2_Start.Range)))*24}

{=sum(({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE})*(Table2_Status.Range = 1)*(if(Table1_Stop.value<Table2_Stop.Range,Table1_Stop.Value,Table2_Stop.Range)-if(Table1_Start.Value>Table2_Start.Range,Table1_Start.Value,Table2_Start.Range)))*24}

{=sum(({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE})*({FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE})*(if(Table1_Stop.value<Table2_Stop.Range,Table1_Stop.Value,Table2_Stop.Range)-if(Table1_Start.Value>Table2_Start.Range,Table1_Start.Value,Table2_Start.Range)))*24}

{=sum(({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE})*({FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE})*(if(Table1_Stop.value<Table2_Stop.Range,Table1_Stop.Value,Table2_Stop.Range)-if(Table1_Start.Value>Table2_Start.Range,Table1_Start.Value,Table2_Start.Range)))*24}

{=sum({0;1;0;1;0;0;0;0}*(if(Table1_Stop.value<Table2_Stop.Range,Table1_Stop.Value,Table2_Stop.Range)-if(Table1_Start.Value>Table2_Start.Range,Table1_Start.Value,Table2_Start.Range)))*24}

{=sum({0;1;0;1;0;0;0;0}*(if(40896.8<Table2_Stop.Range,Table1_Stop.Value,Table2_Stop.Range)-if(Table1_Start.Value>Table2_Start.Range,Table1_Start.Value,Table2_Start.Range)))*24}

{=sum({0;1;0;1;0;0;0;0}*(if({FALSE;FALSE;FALSE;TRUE; FALSE;FALSE;FALSE;TRUE}),Table1_Stop.Value,Table2_Stop.Range)-if(Table1_Start.Value>Table2_Start.Range,Table1_Start.Value,Table2_Start.Range)))*24}

{=sum({0;1;0;1;0;0;0;0}*({40742.7;40799.1;40879.0;40896.8;40736.7;40809.1;40849.0;40896.8}-if(Table1_Start.Value>Table2_Start.Range,Table1_Start.Value,Table2_Start.Range)))*24}

{=sum({0;1;0;1;0;0;0;0}*({40742.7;40799.1;40879.0;40896.8;40736.7;40809.1;40849.0;40896.8}-if(40674.2>Table2_Start.Range,Table1_Start.Value,Table2_Start.Range)))*24}

{=sum({0;1;0;1;0;0;0;0}*({40742.7;40799.1;40879.0;40896.8;40736.7;40809.1;40849.0;40896.8}- if({TRUE;FALSE;FALSE;FALSE;TRUE; FALSE;FALSE;FALSE }),Table1_Start.Value,Table2_Start.Range)))*24}

{=sum({0;1;0;1;0;0;0;0}*({40742.7;40799.1;40879.0;40896.8;40736.7;40809.1;40849.0;40896.8}-{40674.2;40742.7;40799.1;40879.4;40736.7;40809.1;40849.0}))*24

{=sum({0;1;0;1;0;0;0;0}*({68.4;56.4;79.9;17.8;62.4;72.4;39.9;47.8}))*24

{=sum({0;1;0;1;0;0;0;0}*{68.4;56.4;79.9;17.8;62.4;72.4;39.9;47.8})*24

{=sum({0;56.4;0;17.8;0;0;0;0}))*24

{=74.2*24

=1780.7

With the three returned arrays; the product of the three is the sum of the hours between the Rcvd Date Time and the Comp Date Time of each work order.