DaX Month and Year

jersey

New Member
Joined
Mar 9, 2012
Messages
28
Hello. I am a newbie to powerpivot. My Date field in the pivot looks like this 4/20/2012 4:41:45 PM . How can I use New Measure to only show Month and Year. Thank you, Cindy;)
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hi Jersey

Do you want it is just visually look like April 2012 or you want to actually change the information. If it is just the look on the Powerpivot ribbon in the middle you see "Data Type" problably says "date". Underneath is format with a bunch of formats including loads under other. If none of these formats suit you e.g. I like mmm-yy and it isn't there you have to create a new calculated column where <date> is the name of your date column.

=FORMAT(<date>,"Mmm-YY")

If you want to be able to group items together by month or day in your pivot table let me know as need a different approach

Mike
 
Upvote 0
Hello Mike,
Can I create the month year as a new measure.
when i create my pivot table the dates look like this.
4/20/2012 4:41:45 PM
4/20/2012 5:41:45 PM
4/10/2012 4:41:05 PM

Im looking the pivot table to look like this.

4/2012
 
Upvote 0
I'm not 1005 sure on this but your format is not one of the standard formats that you can use to format the existing column. I beleive what you need ot do is create a calcualted column with following formula

Code:
=format([Visit Month],"m/yyyy")

This gives you 4/2012

If you use this column in your pivot table then that's what you will see. Be warned the powerpivot tables only sort dates as text i.e. alphabetically so you wil lget 3/2011 and 3/2012 next to each other. You can resort manually or there is a tricky workaround.

Does that help

Mike
 
Upvote 0
sorry [visit month] is the name of a column I tried it on so just replace with the name of the column that holds the current date.
 
Upvote 0
I double checked this. If you use another column and FORMAT you will get alphabetic sort as soon as you start. If you reformat your exisitng column using the format dropdown on the ribbon and say use "April 2012" it wil ldrop them in the right orer when you first create the pivot. However if you sort it you notice it says "A to Z" not "oldest to newest" so it will mess up.

Absolutely bizare treatment of dates, but there you go. If you can't live with one of the odd choices for standard format then you wil lhave to do some more work to get them in date order.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top