cancel
Showing results for
Did you mean:
Highlighted
Member

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

Accepted Solutions
Member

## 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")`
Member

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

Announcements

#### 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

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 268 members 3,133 guests
Recent signins: