Formula for date Format problem

[Twane]

Hi there

I have a table in PowerPivot 2013 that contains a column "Expected Close Date", now I want to extract the month from the full date in the calculated column & use this formula to do it: =FORMAT(MONTH([Expected Close Date]),"mmmm")

This extracts the name of the month, with the first letter abbreviated....here's my problem...

Somehow it changes all the months to the previous month, what am I doing wrong then. Example:

Expected Close Date Year Month (Calculated Column)
2014-02-28 2014 January



Can anyone tell me how to correct this as I am busy with an important report for my director?

Looking forward to a response.

Thanks
Twane

 

[Luke M]

Your formula takes a Date, and converts it to a number 1-12. It then takes that number and formats it as if it were a date. Thus, 1 = Jan 1 1900, 2 = Jan 2 1900, etc, and thus will always give you "January". This is clearly not what you wanted.

Remove the MONTH function from your formula, and you should be good:
=TEXT([Expected Close Date],"mmmm")

Note: I'm not familiar with PowerPivot 2013, so it may be that it has a special "FORMAT" function. In normal use, I would have expected this to be the TEXT function as I wrote above, hence the change. Please advise if FORMAT is a legitimate function.

 

[Twane]

Hi Luke,

Thanks for the response, however did not get the results.

Please see file attached, the calculated column is in PowerPivot.

I await your response.
Twane

 

[Luke M]

Is this not what you wanted?

 

[Twane]

Hi there Luke

Great stuff, I got my answer. Thank you so much for your help

 

[Debraj]

@Luke M !

Here is the list of all DAX Functions for Power Pivot 2013.
http://powerpivot-info.com/post/52-list-of-powerpivot-dax-functions-with-description

@Twane !

Your data in Table are not actually Date, they are just TEXT.

 

[Luke M]

Thanks Deb, most insightful!