topic Re: To find out school year in DAX Commands and Tips

To find out school year

uc — Sat, 08 Jan 2022 22:25:39 GMT

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.

Re: To find out school year

ValtteriN — Sun, 09 Jan 2022 00:28:37 GMT

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!

Re: To find out school year

uc — Sun, 09 Jan 2022 12:37:54 GMT

Thankyou. It works.

Re: To find out school year

ValtteriN — Sun, 09 Jan 2022 18:55:37 GMT

Hi,

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