Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
BCWdesign
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
mukeshkasi25
Frequent Visitor

Hi @BCWdesign 
Please try this 

 

mukeshkasi25_0-1690960492518.png

 

View solution in original post

5 REPLIES 5
mukeshkasi25
Frequent Visitor

Hi @BCWdesign 
Please try this 

 

mukeshkasi25_0-1690960492518.png

 

grazitti_sapna
Resolver I
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.

Hope this will help you.

Ahmedx
Super User
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")

 

Screenshot_4.png

DOLEARY85
Super User
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"

 

DOLEARY85_0-1690920932329.png

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

DOLEARY85
Super User
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"
    )
 
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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