Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear community!
I have a small problem, I exported data about users and the deafult export is as below...
I'm trying to calculate the average tenure for the user list, unfortunately PowerBi isn't recognizing this format as duration and defaults to Text. I've tried changing the format by transforming the data but keep getting an error :
Expression.Error: We couldn't parse the Duration literal.
Details:
1 year, 4 months and 25 days
I'd appreciate any feedback on how to easily get the average tenure for my user list!
Solved! Go to Solution.
Hi @MiloT
Here is a sample file with the solution https://www.dropbox.com/t/iulSDAx1AvxBTEyo
> New column :
Number of Days =
VAR YearLocation =
FIND ( "year", 'Duration'[Duration], 1 , 0 )
VAR MonthLocation =
FIND ( "month", 'Duration'[Duration], 1 , 0 )
VAR DayLocation =
FIND ( "day", 'Duration'[Duration], 1 , 0 )
VAR NumberOfYears =
IF (
YearLocation <> 0,
VALUE ( LEFT ( 'Duration'[Duration], 2 ) )
)
VAR NumberOfMonths =
IF (
MonthLocation <> 0,
IF (
YearLocation = 0,
VALUE ( LEFT ( 'Duration'[Duration], 2 ) ),
IF (
DayLocation <> 0,
VALUE ( MID ( 'Duration'[Duration], MonthLocation - 3, 2 ) )
)
)
)
VAR NumberOfDays =
IF (
DayLocation <> 0,
IF (
YearLocation = 0 && MonthLocation = 0,
VALUE ( LEFT ( 'Duration'[Duration], 2 ) ),
VALUE ( MID ( 'Duration'[Duration], DayLocation - 3, 2 ) )
)
)
RETURN
NumberOfYears * 365 + NumberOfMonths * 30 + NumberOfDays
This is amazing, thank you so much Tamerj1 - I really appreciate the help, you are a star!
Hi @MiloT
Here is a sample file with the solution https://www.dropbox.com/t/iulSDAx1AvxBTEyo
> New column :
Number of Days =
VAR YearLocation =
FIND ( "year", 'Duration'[Duration], 1 , 0 )
VAR MonthLocation =
FIND ( "month", 'Duration'[Duration], 1 , 0 )
VAR DayLocation =
FIND ( "day", 'Duration'[Duration], 1 , 0 )
VAR NumberOfYears =
IF (
YearLocation <> 0,
VALUE ( LEFT ( 'Duration'[Duration], 2 ) )
)
VAR NumberOfMonths =
IF (
MonthLocation <> 0,
IF (
YearLocation = 0,
VALUE ( LEFT ( 'Duration'[Duration], 2 ) ),
IF (
DayLocation <> 0,
VALUE ( MID ( 'Duration'[Duration], MonthLocation - 3, 2 ) )
)
)
)
VAR NumberOfDays =
IF (
DayLocation <> 0,
IF (
YearLocation = 0 && MonthLocation = 0,
VALUE ( LEFT ( 'Duration'[Duration], 2 ) ),
VALUE ( MID ( 'Duration'[Duration], DayLocation - 3, 2 ) )
)
)
RETURN
NumberOfYears * 365 + NumberOfMonths * 30 + NumberOfDays
User | Count |
---|---|
57 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |