Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey all,
I have a report that is published daily and I would like to see the "average" of the time it is reported during different periods. Basically I would like to have a time visual with the time the report is sent out on every day, plus a gauge/KPI measure with the average time it is sent out on the selected month. I can't seem to get it working properly. I now have two semi-solutions, but cannot have them in one.
I made a measure
Average Time =
Subject | Date | Time | Hour | Minute | Hour Decimal |
Report 06 March 2023 | 08/03/2023 | 10:26:21 | 10 | 26 | 10,43333333 |
Report 03 March 2023 | 07/03/2023 | 13:36:20 | 13 | 36 | 13,6 |
Report 02 March 2023 | 06/03/2023 | 11:16:48 | 11 | 16 | 11,26666667 |
Report 01 March 2023 | 03/03/2023 | 09:57:43 | 9 | 57 | 9,95 |
Report 28 February 2023 | 02/03/2023 | 15:42:29 | 15 | 42 | 15,7 |
Report 27 February 2023 | 01/03/2023 | 13:38:48 | 13 | 38 | 13,63333333 |
Report 24 February 2023 | 28/02/2023 | 10:30:04 | 10 | 30 | 10,5 |
Report 23 February 2023 | 27/02/2023 | 09:32:23 | 9 | 32 | 9,53333333 |
Report 22 February 2023 | 24/02/2023 | 13:49:46 | 13 | 49 | 13,81666667 |
Report 21 February 2023 | 23/02/2023 | 10:52:10 | 10 | 52 | 10,86666667 |
Report 20 February 2023 | 22/02/2023 | 14:26:17 | 14 | 26 | 14,43333333 |
Report 17 February 2023 | 21/02/2023 | 11:10:03 | 11 | 10 | 11,16666667 |
Report 16 February 2023 | 20/02/2023 | 12:25:33 | 12 | 25 | 12,41666667 |
Report 15 February 2023 | 17/02/2023 | 14:01:36 | 14 | 1 | 14,01666667 |
Report 14 February 2023 | 16/02/2023 | 16:15:52 | 16 | 15 | 16,25 |
Report 13 February 2023 | 15/02/2023 | 10:53:50 | 10 | 53 | 10,88333333 |
Report 10 February 2023 | 14/02/2023 | 09:32:52 | 9 | 32 | 9,53333333 |
Report 09 February 2023 | 13/02/2023 | 09:38:10 | 9 | 38 | 9,63333333 |
Report 08 February 2023 | 10/02/2023 | 09:31:08 | 9 | 31 | 9,51666667 |
Report 07 February 2023 | 09/02/2023 | 11:58:55 | 11 | 58 | 11,96666667 |
Report 06 February 2023 | 08/02/2023 | 10:42:16 | 10 | 42 | 10,7 |
Report 03 February 2023 | 07/02/2023 | 09:58:36 | 9 | 58 | 9,96666667 |
Report 02 February 2023 | 06/02/2023 | 16:18:20 | 16 | 18 | 16,3 |
Report 01 February 2023 | 03/02/2023 | 10:19:02 | 10 | 19 | 10,31666667 |
Report 31 January 2023 | 02/02/2023 | 14:03:17 | 14 | 3 | 14,05 |
@Olaf18 , Assume Hour Decimal is a measure
then create a new measure like
Time(0,0,0) + [Hour Decimal]/24
or refer
Duration
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK...
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389
@amitchandak
If I try this, I do get an average Time variable again, but I run into the same issue that I can't then display this measure in a visual such as a KPI measure, only as a Card. However, I would like to see this evolving over time. With the time, I can make a nice Card saying "Average time last month is 10:55", but I would also like to see this evolving over time and then the measure can't be selected. Only the "Hour Decimal" can be used but then it does'nt make sense:
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |