mr. Gaston july easy excel powerpivot
for the data analyst chapter 6
calculated columns
hey welcome back to the mr. excel and
that cast I'm diljaan well this chapter
we're taking a look at how to use the
dax language right in the power pivot
grid to add new calculated columns
before we do that though just in case
you're not familiar with these functions
in Excel let's talk about some great
date handling functions in excel so
let's say that we have a date up here
july 676 2010 and I want to break that
down into a year month and day excel
gives us three functions that make this
really really easy so equal year of that
date will give us 2010 equal month on
that date will give us 74 july and equal
day of that date will give us just the
sixth ok so those are built-in
unfortunately they give us numbers of
course numbers are easy to sort but you
know it may not be how we want things to
appear so lots of times i will use the
text function text function so equal
text of that date
press f4 there to lock that down and
then say format it as shown here to the
left so if we use the mmm format you see
we get ju l4 july i'll double click to
copy this down for em's will actually
spell out the month of july a single d
will give us the day in a single digit
for dates first through the 9th or two
digits for 10th through the 31st but if
you specify DD then you get the very
nice sort friendly two digits all the
time so instead of six you get 06 3ds
gives you the day of the week
abbreviation for DS actually spells out
the day of the week and then you can
start to get creative for example ymm
would give you the year a dash and then
the two digit abbreviation or let's try
YY dash
mmm to actually give us the year and the
month and that was gonna be really
really hard to sort properly in
PowerPivot so that was probably not even
practical
ok so let's go and see how we can use
that knowledge from excel in the
powerpivot windows we have a date field
here and we want to add a new calculated
column so we just start with an equal
sign and type the function that your
started when you saw me use the year
function notice as soon as i type why
they offer a couple of choices here i'll
press tab to choose year and then they
want to know which field ok now hardcore
excel people know that we can use the
arrow keys to go back to that field and
fortune powerpivot does not support the
arrow keys you have to either start to
type the field name or just use the
mouse to click on it I know that's
hardcore people hate to use the mouse
yeah but here we are that's it's just
the what we have to do click the closing
parenthesis and very quickly it will
give us the
year calling for those 1.8 million rose
again bad heading out which they would
just prompt us for the headings we're
going to have to change that heading
every single time i'll call this year
right and I could do similar things for
a month and four day using the month and
day functions again most functions that
we use here in the script are exactly
like the functions and we use Excel the
one glaring difference is the text
function which is so commonly used in
Excel is not spelled text here is billed
as format so we use equal format click
on the field we want a comma and then in
quotes whatever format we would like to
use I'm gonna spell of the Month
completely that would be 4 m's in quotes
closing quote closing parenthesis press
ENTER and let that calculate down
and again once we get the answer you
have to right click and change that
horrible heading to something a bit more
friendly so there you have it just a
sampling of some of the many Dax
functions that you can use to create new
calculations right here in the grid
chapter 6 has a complete list of all
those Dax functions and examples of how
to use them for this is the one that you
run into most often sell offered this is
what we do in the podcast i want to
thank you for stopping by
see you next time for another NetCast
from mr excel