cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

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:

Month Wrong.PNG

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])
1 ACCEPTED SOLUTION

Accepted Solutions

Re: Conflicting Month Calculations

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")
1 REPLY 1

Re: Conflicting Month Calculations

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")

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 268 members 3,133 guests
Please welcome our newest community members: