Forum Discussion

MiloT's avatar
MiloT
Frequent Visitor
4 years ago
Solved

Help with tenure format - average overview

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'...
Help appreciated
  • tamerj1's avatar
    tamerj1
    4 years ago

    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
tamerj1's avatar
tamerj1
Community Champion
4 years ago

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