DaX Month and Year

[jersey]

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

 

[masplin]

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]

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]

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

=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]

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]

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.

 

[jersey]

Mike this is so cool. You are awesome. Thank you.