Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |