Help with tenure format - average overview

[MiloT]

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! 

[MiloT]

This is amazing, thank you so much Tamerj1 - I really appreciate the help, you are a star!

[tamerj1 · Accepted 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