Conflicting Month Calculations

[Anonymous]

Here's my sample data, I have been creating some calculated columns that all look at the Date_Time field and determine it's academic year - I am trying to extend this to enable drill down on a monthly and daily basis, whereby September is month 1 through to month 12 August. However there's a discrepancy in the way the month is calculated...

Here's my data:

We can see something is going wrong in the Academic Year-Month column where the 12th January has become Month 5 - December (December should actually be Month 4)

Date_Time is the native date/time field.

Here's my formulas for the other calculated columns

Academic Year = IF(MONTH('Views and Created'[Date_Time])<9,YEAR('Views and Created'[Date_Time])-1&"/"&RIGHT(YEAR('Views and Created'[Date_Time]),2),YEAR('Views and Created'[Date_Time])&"/"&RIGHT(YEAR('Views and Created'[Date_Time])+1,2))

This works fine.

This is the interesting one:

Academic Year-Month = 'Views and Created'[Academic Year] & " - " & 
IF(
    IF(MONTH('Views and Created'[Date_Time])>8,MONTH('Views and Created'[Date_Time])-8,MONTH('Views and Created'[Date_Time])+4)<10,0,""
)&
IF(MONTH('Views and Created'[Date_Time])>8,MONTH('Views and Created'[Date_Time])-8,MONTH('Views and Created'[Date_Time])+4)
&" "
& FORMAT(MONTH('Views and Created'[Date_Time]),"MMM")

Lets break down how 12/01/2018 becomes "05 Dec". The first part works correctly. The statement looks at the date, sees that its January (01) - goes to the false part of the IF statement and adds 4 to this figure and it becomes 5. This is correct as January is month 5 of the academic year.

The final line however looks at the 12/01/2018 and for some unknown reason it now looks at the 12 value and determines the field is December.

My question is why does the Month function, when evaluating 12/01/2018 in one instance look at the "12" figure" and in the other instance look at the "01" figure?? Why the inconsistency?

For reference my 3rd calcualted column Academic Year-Month-Day, the Day portion of the formula works correctly and consistently:

Academic Year-Month-Day = 'Views and Created'[Academic Year-Month] & " - "
& IF(DAY('Views and Created'[Date_Time])<10,0,"")
& DAY('Views and Created'[Date_Time])

[Anonymous · Accepted solution]

So I couldn't see the woods for the trees - the reason the final statement didn't work is because I didn't need to use the month function here.

This:

& FORMAT(MONTH('Views and Created'[Date_Time]),"MMM")

Should be:

& FORMAT('Views and Created'[Date_Time],"MMM")