display calculated values for a timespan

[sangrick]

Hello,

I'm new to Power BI and I'm stuck with a problem.

I have a table with calls including the fields customer, start_date and end_date

No I want to see how many call are active during a given period (line chart or similar over the period would be good)

e.g.

Customer	Start_Date	End_Date
A	27.06.2017 23:31	28.06.2017 00:27
A	27.06.2017 23:31	28.06.2017 01:35
B	27.06.2017 23:43	28.06.2017 00:09
B	27.06.2017 23:45	28.06.2017 00:09
C	27.06.2017 23:48	28.06.2017 00:06
C	27.06.2017 23:48	28.06.2017 00:12

Should result in an image which shows

from 23:31 to 23:43 : 2
from 23:43 to 23:45 : 3
from 23:45 to 23.48 : 4
from 23:48 to 00:06 : 6
from 00:06 to 00:09 : 5
from 00:09 to 00:12 : 3
from 00:12 to 00:27 : 2
from 00:27 to 01:35 : 1

The time should be on X-Axis and the number on Y-Axis (If not all customers are selected they should not be included in the number)

It could be sufficient to have the calculation not for each minute, but for a small delta of 5 minutes

Any ideas how to achieve this?

My idea was to create a temporary table with calculated values for small periods

Period		A	B	C
23:30	23:35	2
23:35	23:40	2
23:40	23:45	2	1
23:45	23:50	2	2	2
23:50	23:55	2	2	2
23:55	00:00	2	2	2
00:00	00:05	2	2	2
00:05	00:10	2	2	2
00:10	00:15	2		1
00:15	00:20	2
00:20	00:25	2
00:25	00:30	2
00:30	00:35	1
00:35	00:40	1
00:40	00:45	1
00:45	00:50	1
00:50	00:55	1
00:55	01:00	1
01:00	01:05	1
01:05	01:10	1
01:10	01:15	1
01:15	01:20	1
01:20	01:25	1
01:25	01:30	1
01:30	01:35	1
01:35	01:40
01:40	01:45

but I don't know how to do that 😞

I need a column for each customer (customers can change) and I need a calculation which includes fields from a differen table.

All I tried with calculated columns was that I can only access fields of the same table.

Any help would be appreciated.

regards

Stephanie

[v-huizhn-msft · Accepted solution]

Hi @sangrick,

You idea is to create a temporary table a small delta of 5 minutes, it's hard to compare the time wiout date. For example, 28.06.2017 00:27 is bigger than 27.06.2017 23:31 obviously. But 00:27 is less than  23:31 when we use time to compare.

So I post my solution, please follow the steps below.

1. Create a time table including all start_time and end_time, create a new table by clicking "New Table" under Modeling on Home Page.

TimeTable = UNION(DISTINCT('Fact'[Start_Date]),DISTINCT('Fact'[End_Date]))

2. Order the Start_Date column Ascending(right click the head name->Ascending), add a rank column, then get the period end time using the rank.

rank = RANKX(TimeTable,TimeTable[Start_Date],,ASC)

End_Time = LOOKUPVALUE(TimeTable[Start_Date],TimeTable[rank],TimeTable[rank]+1)

You will get the TimeTable like the screenshot.



3. Create A,B, C calculated columns using the formulas.

A = CALCULATE(COUNTROWS('Fact'),FILTER('Fact','Fact'[Customer]="A"&&'Fact'[Start_Date]<=TimeTable[End_Time]&&'Fact'[End_Date]>=TimeTable[End_Time]))

B = CALCULATE(COUNTROWS('Fact'),FILTER('Fact','Fact'[Customer]="B"&&'Fact'[Start_Date]<=TimeTable[End_Time]&&'Fact'[End_Date]>=TimeTable[End_Time]))

C = CALCULATE(COUNTROWS('Fact'),FILTER('Fact','Fact'[Customer]="C"&&'Fact'[Start_Date]<=TimeTable[End_Time]&&'Fact'[End_Date]>=TimeTable[End_Time]))

4. Create a stacked area chart using the TimeTable, select the start_time as Axis, and display it as category. Please see the following screenshot.

Best Regards,
Angelia

[sangrick]

works nearly perfect.

Sometimes I have a timestamp in start_date and in end_date, this causes that the lookup for End_Time doesn't work.

e.g with my real data I have

Start Date; rank; End_Time

28.09.2016 09:00:54; 30; 28.09.2016 09:00:59

28.09.2016 09:00:59; 31;

28.09.2016 09:00:59; 31;

28.09.2016 09:01:01; 33; 28.09.2016 09:01:06

I change the table creation to

TimeTable = DISTINCT(UNION(DISTINCT('Fact'[Start_Date]),DISTINCT('Fact'[End_Date])))

Additionally I added columns for hour and minute to drill down

🙂

many thanks