topic Re: Age in Years Months in DAX Commands and Tips

Age in Years Months

duesouth — Fri, 21 Jun 2024 10:30:17 GMT

I work in a school. We test the pupils on their reading ages. This is stored in our database/management information system in a odd format - e.g. 11/2 is 11 Years, 2 months; 15/10 is 15 years, 10 months.

I'm trying to get these reading ages into Power BI. I've got a report out of our database, but am struggling to get the data into a table. I tried splitting the columns in Excel first and having them in the format: 11 years, 2 months but Power BI is picking that up as a text field. I can't seem to change the format in power query - I just get errors on the whole column. I'd like Power BI to give me average reading ages etc. Any ideas how I can get this into Power BI in a useable format? Does it need to be in a different format in Excel first? I've spent this morning searching for solutions, but can't find anything. Would appreciate any help!

Pupil ID	Reading Age
1	11 years, 2 months
2	13 years, 2 months
3	14 years, 7 months
4	17 years, 0 months
5	15 years, 6 months
6	13 years, 2 months
7	16 years, 5 months
8	14 years, 0 months
9	13 years, 8 months
10	15 years, 6 months
11	10 years, 5 months
12	5 years, 0 months
13	17 years, 0 months
14	15 years, 10 months
15	17 years, 0 months
16	17 years, 0 months
17	17 years, 0 months
18	16 years, 9 months
19	17 years, 0 months

Re: Age in Years Months

bhanu_gautam — Fri, 21 Jun 2024 10:40:11 GMT

duesouth , You can try to split this Age column in excel using text to column function and convert the datatype into number and than try to load it into Power BI then

Re: Age in Years Months

duesouth — Fri, 21 Jun 2024 10:43:29 GMT

Thanks for the reply. Have two separate columns - one for year and one for month? Would Power BI then average? E.g. if I had

13 years, 1 month

13 years, 3 months

Would Power BI then give me an average for that "group" of 13 years, 2 months?

Re: Age in Years Months

duesouth — Fri, 21 Jun 2024 12:10:56 GMT

I've got the data into Power BI. Years in one column, months in another. The trouble when I average for a year group the average is 12.41 years and 4.28 months. The .41 years would need to be added to the months, if you are with me? Is there no way to store years and months together in one column - and then an average would come out something like 12 years 7 months?

Re: Age in Years Months

Anonymous — Mon, 24 Jun 2024 02:29:33 GMT

Hi, duesouth
I'm using your original table and first create new columns to separate year and month:

Years = LEFT([Reading Age], SEARCH(" ", [Reading Age])-1) Months = MID([Reading Age], SEARCH(",", [Reading Age])+2, LEN([Reading Age])-SEARCH(",", [Reading Age])-8)

Then convert them to Whole number and put it into the view as an Average

Select slicer, here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.