Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I'm working as an Analyst at a Call Center and work with a lot of timestamps.
So several agents will have statistics like a call at 8:00 am was 00:22:40.
The method:
I have converted the times into seconds within Excel.
Cell*86,400.
Then within PowerBI, I apply the following formula for dd:hh:mm:ss
Avg Handle Time Measure =
VAR Duration = AVERAGE('FACT Agent'[Avg Handle Time])
VAR Days =
INT ( Duration / 86400)
VAR Hours =
INT ( MOD( Duration - ( Days * 86400),86400) / 3600)
VAR Minutes =
INT (MOD (MOD( Duration - (Days * 86400 ),86400 ), 3600 ) / 60)
VAR Seconds =
ROUNDUP(MOD ( MOD( MOD ( Duration - (Days * 86400 ),86400 ), 3600 ), 60), 0)
VAR D =
IF ( LEN ( Days ) = 1,
CONCATENATE ( "0", Days ),
CONCATENATE ( "", Days )
)
VAR H =
IF ( LEN ( Hours ) = 1,
CONCATENATE ( "0", Hours ),
CONCATENATE ( "", Hours )
)
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
)
VAR S =
IF (
LEN ( Seconds ) = 1,
CONCATENATE ( "0", Seconds ),
CONCATENATE ( "", Seconds )
)
RETURN
CONCATENATE (
D,
CONCATENATE ( ":", CONCATENATE ( H, CONCATENATE( ":", CONCATENATE (M, CONCATENATE ( ":", S ) ) ) )
))
or if it is a mm:ss time:
Average Speed of Answer Measure =
VAR Duration = AVERAGE('FACT CSQs'[Avg Speed of Answer in Seconds])
VAR Minutes =
INT( Duration / 60)
VAR Seconds =
ROUNDUP(MOD ( Duration - (Minutes / 60 ), 60), 0 )
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
)
VAR S =
IF (
LEN ( Seconds ) = 1,
CONCATENATE ( "0", Seconds ),
CONCATENATE ( "", Seconds )
)
RETURN
CONCATENATE (M, CONCATENATE ( ":", S ) )
The Problem:
However this only converts the timestamps into text and therefore only works within measures, and not actual calculated columns. I need to be able to use hh:mm:ss as additives. Currently within graphs, I can use a work-around solution as a measure, as some have suggested, but I am asking for help now to use the times as numbers so I can run further analytics.
Any help would be greatly appreciated.
Hi , @AZJohnPowerBI
Based on your description, you want to process your timestamp text and want to return the number format. Right?
For handling text formatting, we recommend that you implement it in Power Query Editor, and you can try using Duration functions to manipulate timestamps.
For more information, you can refer to :
Duration functions - PowerQuery M | Microsoft Learn
For the specific format you want to work with, you can provide us with some of your sample data, as well as the output format you want, so that we can better help you.
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |