Problem Getting Month

[rhinofromTurkeyParticipant Started: 0Replies: 7Total: 7]

I have a table I imported from SQL Server  – its a subset of data from a program we use to gather User Login Stats

The table structure is as follows

UserName |  Department  |  Login_Date
============================

A | HR | 2015-03-25
B | Research| 2015-04-12
C|Finance| 2015-04-25

 

I imported the table into the PowerPivot Window, from there I used the formula window and got the Year (YEAR[Login_Date]), Numerical_Month(MONTH[Login_Date] – which is all great; however, I would like the actual Month Name.  Figuring I could do this and since I have values I extracted already this “should” be easy.

I tried the following formulas
Format((Month[Login_Date]),”MMM”)  = gives me all Jan Or JAN and Dec – not the correct one
Format([Login_Date],”MMM”) = gives me same as above

I can do Format((Month[Login_Date]),”00″)  (two zeros) and I will get the correct month – :-/

BUT changing 00 -> MM (or MMM) gives me a wierd answer – all Jan/Dec

Would it be better to possibly do this in SQL Server?  I’m a little lost for what to try – I thought it was better to keep the SQL server table small and allow PowerPivot to do its thing.

Thanks for all the help

Rhino

 

 

[rhinofromTurkeyParticipant Started: 0Replies: 7Total: 7]

I believe after reading the two articles – I need a date/calendar table to cross reference/connect

 

http://www.powerpivotpro.com/2014/02/the-ultimate-date-tablerevisited/

http://www.powerpivotpro.com/2011/11/the-ultimate-date-table/

 

Thoughts?

 

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hello Rhino,

Let me evaluate your sample and will get back to you shortly.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

When I use the formula:

= FORMAT( Month[Login_Date], “MMM” ) or = FORMAT( Month[Login_Date], “MMMM” ) I get the expected month

Your formula

Format((Month[Login_Date]),”MMM”)

appears to have some extra parentheses — before Month and after ] — . Maybe since there is a DAX function MONTH, the extra parentheses are sending the wrong message. Also make sure your formula begins with an equals sign.

[rhinofromTurkeyParticipant Started: 0Replies: 7Total: 7]

tomallen:

my apologies i meant to add the = on the original post

Thank you for your reply; however, I’m unsure how your formula is working????

When I type =MONTH[Login_Date]  – I get an #ERROR and when I select Show Error it states – Cannot find table “MONTH”

I get the same error when I type =FORMAT(MONTH[Login_Date],”MMM”)

Am I doing something incorrect?

Thanks for your assistance

rhinofromturkey

 

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hello Rhino,

When you imported the data into power pivot, what did you name the table for this structure:

UserName | Department | Login_Date
============================

A | HR | 2015-03-25
B | Research| 2015-04-12
C|Finance| 2015-04-25

Your formula should follow this pattern:

=FORMAT(‘YourPowerPivotTableName'[Login_Date],”MMM”)

[rhinofromTurkeyParticipant Started: 0Replies: 7Total: 7]

tomallen:

Thank you again for your prompt response

When I use =FORMAT(Stats[Login_Date],”MMM”) I get the same value as the Login_Date table

I did find a work around; however, I’m not so sure how to import it into the PowerPivot Designer

If I use in the Excel window in a Column I create:

=Text([Login_Date],”MMM”)

This gives me the correct month – not sure  if its as effective but its giving me the result.

What are your thoughts/professional opinion?  Should I use excel or should I use powerpivot?

Thanks

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hello Rhino,

It would make more sense to me that you use SQL before importing into Power Pivot, as you mentioned a couple of posts ago. But I think I understand what is going on.

Take a look at the attached spreadsheet. Go to Power Pivot window and check out the results.

Your “date” column is probably imported as text. For the formatted columns, check-out that the ones that convert correctly were formatted using a date-type column, but then look at the Text_Conversion column that is based on a column that in all respects looks like a date, but is actually text.

To demonstrate, select the Login_Date_As_Text column within the Power Pivot window. Then, on the Home tab, in the Formatting options group, change the data type to date. After the data type changes, you will see an interesting change in the Text Conversion column.

Attachments:

You must be logged in to view attached files.

[rhinofromTurkeyParticipant Started: 0Replies: 7Total: 7]

Tomallen:

Just wanted to say thanks for all your help – again.  I really do appreciate your time.

After going through all the calendar posts, and how they said having a calendar table would make things easier – I ended up making my own calendar along with a column that matched my Login_Date field.  I then created a relationship for the two tables (new calendar field and login_date) and then placed the slicers in the excel file.

This might be excessive??!? but I’m getting the information I want and it’s displaying the Month Name (vs Month Number) which is GREAT.

And yes I believe you are correct about the date being being imported as Text from the SQL server which is what was causing the issue – although it doesn’t make sense as to why the Month number would be generated with the formula and not the Month Name.  Either way it’s good –

Thanks again

RhinoFromTurkey

 

[Author]

Posts