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.
Hi all,
I try to calculate the average "duration of activity" during time.
TaskNr | Start | End | Duration |
1 | 1/07/2018 | 14/07/2018 | 14 |
2 | 1/07/2018 | 22 | |
3 | 2/07/2018 | 5/07/2018 | 4 |
4 | 10/07/2018 | 14/07/2018 | 5 |
5 | 25/07/2018 |
I calculated the duration compared to today by next formula:
= Table.AddColumn(#"Type gewijzigd", "Duration", each if DateTime.Date(DateTime.LocalNow()) < [Start] then
null
else
Number.From(List.Min({DateTime.Date(DateTime.LocalNow()),[End]}) - [Start])+1)
The average activity compared to today is 11,3.
What i try to do is become a table where I compare the duration according to a calendar instead of only today.
This is table I created Manually:
DateAverage
1/07/2018 | 1,0 |
2/07/2018 | 1,7 |
3/07/2018 | 2,7 |
4/07/2018 | 3,7 |
5/07/2018 | 4,7 |
6/07/2018 | 5,3 |
7/07/2018 | 6,0 |
8/07/2018 | 6,7 |
9/07/2018 | 7,3 |
10/07/2018 | 6,3 |
11/07/2018 | 7,0 |
12/07/2018 | 7,8 |
13/07/2018 | 8,5 |
14/07/2018 | 9,3 |
15/07/2018 | 9,5 |
16/07/2018 | 9,8 |
17/07/2018 | 10,0 |
18/07/2018 | 10,3 |
19/07/2018 | 10,5 |
20/07/2018 | 10,8 |
21/07/2018 | 11,0 |
22/07/2018 | 11,3 |
23/07/2018 | 11,5 |
24/07/2018 | 11,8 |
25/07/2018 | 9,8 |
26/07/2018 | 10,2 |
27/07/2018 | 10,6 |
28/07/2018 | 11,0 |
29/07/2018 | 11,4 |
30/07/2018 | 11,8 |
31/07/2018 | 12,2 |
Could someone help me to create it automatically through Power BI?
Thanks!
Dirk
Solved! Go to Solution.
Hi @v-danhe-msft,
Thanks a lot for trying to help me!
For each day I calculated according to this reasoning:
each if DateTime.Date(Specific Day) < [Start] then
null
else
Number.From(List.Min({DateTime.Date(Specific Day),[End]}) - [Start])+1)
and the I took the average for that Specific Day.
In the meantime I have found a solution.
For both tables (activities and Calendar) I added a Column with the number 1 in each Row.
Then I merge the two tables with Column "Nr" as connection.
With the new merged table I calculate the Duration with "Date" as reference.
Finally I group this table by "Date" and I take the Average of "Duration".
This is how I obtain my expected table 🙂
Regards,
Dirk
Hi @Dirk
From your description, I could not figure out the data you posted in the ‘DataAverage’ table? Could you offer me more information about how you calculate the average activity?
Regards,
Daniel He
Hi @v-danhe-msft,
Thanks a lot for trying to help me!
For each day I calculated according to this reasoning:
each if DateTime.Date(Specific Day) < [Start] then
null
else
Number.From(List.Min({DateTime.Date(Specific Day),[End]}) - [Start])+1)
and the I took the average for that Specific Day.
In the meantime I have found a solution.
For both tables (activities and Calendar) I added a Column with the number 1 in each Row.
Then I merge the two tables with Column "Nr" as connection.
With the new merged table I calculate the Duration with "Date" as reference.
Finally I group this table by "Date" and I take the Average of "Duration".
This is how I obtain my expected table 🙂
Regards,
Dirk
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |