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

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

masplin

Active Member
Joined
May 10, 2010
Messages
406
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
 

jersey

New Member
Joined
Mar 9, 2012
Messages
28
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
 

masplin

Active Member
Joined
May 10, 2010
Messages
406
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
 

masplin

Active Member
Joined
May 10, 2010
Messages
406

ADVERTISEMENT

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.
 

masplin

Active Member
Joined
May 10, 2010
Messages
406
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,738
Messages
5,524,546
Members
409,583
Latest member
gkarthick

This Week's Hot Topics

Top