Forum Discussion
Conflicting Month Calculations
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])