To find out school year

[uc]

Hello

In UK, children start school in September after their 4th birthday. In a given cohort I need to filter only reception age group. For e.g. children starting in Sep 2021, will have their DOB between 01/09/ 2016 to 31/08/2017

I am able to filter the cohort using excel as shown in the attached snapshot. I am interested in only Year 0 (Reception) cohort. However, when I tried to upload the data and tried to calcualte the school year in Power BI I get different result which isn't correct. Attached Power BI snapshot too. Can you please advise where I could have got it wrong?  Thanks in advance.

[ValtteriN · Accepted solution]

Hi,

Here is a modified DAX that should work:

Year = IF(ISBLANK([DOB]),BLANK(),"Year "&

IF(MONTH(DOB[DOB])<9,DATEDIFF(DOB[DOB],TODAY(),YEAR)-4,

DATEDIFF(DOB[DOB],TODAY(),YEAR)-5))

End result:

So in this example the only child starting this year would be the one born in 1.1.2018. This checks out since during this year's September they have turned four.

Regarding your original formula. It is not usually a good idea to use days in formulas (like with 122) so I used different method in my example. 

I hope this helps to solve your issue and if it does consider accepting this as a solution and giving the post a thumbs up!

[uc]

Thankyou. It works.

[ValtteriN]

Hi,

Could you mark my post as a solution? That helps other to find the answer easier in the future.