Converting years and months to time data type

[Anonymous]

i am looking to see if there is a simple way to convert values like the following 0 Yrs 7 Mths into actual time so i can start calculating averages etc

[mahoneypat]

Here is an expression you can use in a custom column to convert your text yrs and months to the # of months, for example.  Replace [TimeInYrsMos] with your actual column name.

= let numberlist = List.RemoveNulls(List.Transform(Text.Split([TimeInYrsMos], " "), each try Number.FromText(_) otherwise null)) in numberlist{0}*12 + numberlist{1}

Regards,

Pat

[PhilipTreacy]

Hi @Anonymous 

You could convert to a duration in days/h/m/s but how do you convert 0 Yrs 7 Mths to a date?  You'll need to supply some examples of your desired results.

Thanks

Phil

[Anonymous]

@PhilipTreacy i am looking for a duration, but was hoping i could do duration by years and months which i am guessing i can not do that?

[Fowmy]

@Anonymous 

Refer to below article from REZA and modify as per your need: https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-using-dax

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Fowmy]

@Anonymous 

Can you provide some examples with the desired results?

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

@Fowmy  ideally formatted as a time data type. 00:00:00 so year/months/days. That way i can look at averages/totals

[mahoneypat]

I don't think you will be able to do averages/totals with year/month/days in that format.  How about just using days or months in decimal form?  Your initial example didn't have days in it, but your last post did.  Is your data in this format  0 yrs 6 mos 11 days ?

Regards,

Pat