Age in Years/Months

[duesouth]

I work in a school. On our management information (SIMS), pupils have a recorded reading age. This is stored in SIMS as 13/4, 11/10 etc. which represents Year / Month (e.g. 13 years, 4 months; 11 years, 10 months).

I've been trying to get this reading age into Power BI (or Excel first) in a format where I can then have an average in Power BI to which staff can then apply various slicers (year group, free school meals etc. etc.), but have been striking out. I've managed to get a figure in Power BI for an individual in the 13 years, 4 months format - but when I put a card visual onto the page the only options I have are first, last, count and count distinct, so Power BI isn't storing this as a time period, but a text field - so I can't average.

(The formula I used (after converting years and months into months) is:

Reading Age =
var year1= INT('Reading Ages'[RA in Months]/12)
var month1='Reading Ages'[RA in Months]- year1*12
return
SWITCH( TRUE(),
'Reading Ages'[RA in Months]<12,"0 Year "& 'Reading Ages'[RA in Months] & " Months",
'Reading Ages'[RA in Months]=12,"1 Year "& "0 Months",
year1& " Years " & month1 & " Months")

Is there a way to get this into a format from where I can average pupil's reading ages? Perhaps do this in Excel first and then have Power Query recognise the format as a duration?

Many thanks in advance for any help.

 

[duesouth]

Figured it out! Put age in months into Power BI - then did an AVERAGE of the reading age and then applied the formula above to the average measure.