cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Regular Visitor

## Convert months into years and months

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

1 ACCEPTED SOLUTION
Frequent Visitor

Hi @BCWdesign

5 REPLIES 5
Frequent Visitor

Hi @BCWdesign

Resolver I

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.

Super User

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")``````

Super User

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"

Super User

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"
)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors