Convert months into years and months

[BCWdesign]

Hi

I hope someone can help

I have a column in powerbi which shows months

E.g 18 months

I want a formulae to create a column which converts this to years and months

E.g 1 year 6 months

Hopefully this can be achieved with a simple measure 

[mukeshkasi25 · Accepted solution]

Hi @BCWdesign 
Please try this 

[grazitti_sapna]

Hi @BCWdesign ,

You can try using:-

convert into year = 
VAR _year = INT([Value]/12)
VAR _Month = [Value]-_year*12
RETURN
SWITCH(
TRUE(),
[Value] < 12,"0 Year, " & [Value] & " Month",
[Value] =12 ,"1 Year, " & "0 Month",
_year &" Year, " &_Month & " Month")

Thank you.

Hope this will help you.

[Ahmedx]

pls try this

Column = 
VAR _year = INT([Value]/12)
VAR _Month = [Value]-_year*12
RETURN
SWITCH(
    TRUE(),
    [Value] < 12,"0 Year, " & [Value] & " Month",
    [Value] =12 ,"1 Year, " & "0 Month",
    _year &" Year, " &_Month & " Month")

[DOLEARY85]

Additionally this can be done in power query and then it doesn't matter if you have the word months in the original field or just the number. you can just use the below in a custome column:

if Number.RoundDown([Custom]/12) <> 0 then

Number.ToText(Number.RoundDown([Custom]/12)) & " " & "Years" & " " &

Number.ToText(Number.Mod([Custom],12))& " " & "Months" else Number.ToText(Number.Mod([Custom],12)) & " " & "Months"

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[DOLEARY85]

Hi,

provided the original months column field is just a number you can use:

Column = VAR _year =

    ROUNDDOWN ( DIVIDE ( 'Table (2)'[Column1], 12 ), 0 )

RETURN

    IF (

        _year <> 0,

        _year & " Year "

            & MOD ( 'Table (2)'[Column1], 12 ) & " Months",

        MOD ( 'Table (2)'[Column1], 12 ) & " Months"

    )

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍