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.
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
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 |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
68 | |
48 | |
45 | |
18 | |
15 |