PowerPivot Charts and Trendlines

Last time, I defined what a moving average is and why you might need one.  I then showed you how to calculate a moving average value in your Pivot table and then display it in a chart.  I close by asking the question whether there was a easier way to draw the moving average on your chart.  If all you need is to display the moving average on your chart and do not care what the values are, you can simply plot the [SalesAmount] field as a function of the day on the chart and then follow the simple steps that follow here.

If I right click inside the chart’s plot area (where the data is), I see a popup menu.  Near the bottom of this menu I next click the Add Trendline option which displays the following panel.

The Trendline options begin with six different ways you can ask Excel to fit a line through the data points including everything from a straight line through polynomial, exponential, logarithmic, and power functions.  However, the one I am interested in today is the last one, Moving Average.

Notice that after selecting this option, you have to define the number of period to include in the moving average where period is define by the units of the chart’s x-axis.  If we were to select 4, the resulting line would exactly duplicate the line I already displayed using my calculated 4-day average.  However, as I increase the number of days in the moving average, the fluctuations even out.  The following figure shows a 15-day moving average as a green line which now clearly shows the sales rising through the end of July and then dropping off in August.

So if I can create a Moving Average by simply using the Trendline options for a chart, why would I ever go through the process of calculating the moving average numbers manually?  The simple reason is that the Trendline option does not display the moving average values.  On the other hand, I can always display a table of my daily sales along with the moving average values as shown in this figure.

Well, I know this topic was a little short today, but I’m at SQL Saturday Orlando conducting a presentation on the new features in PowerPoint 2012.  If you have never been to a SQL Saturday event, you owe it to yourself to go to http://www.sqlsaturday.com and find out when the next one is in your area.  It is a great way to get a day of free training on SQL Server and related topics (like PowerPivot) and to network with your peers of similar interest.

C’ya next time.

Advertisements

How To Find Something To Write About

I’ve had people ask, ‘How do you find something to write about every week?  Don’t you run out of topics?’

Actually it is a lot easier than you may think.  All you need to do is to be observant about the world around you.  On any given day, I’m sure you have encountered something that so amazed you, so disturbed you, or so angered you that you immediately needed to tell your friends about it.  Writing a blog is really nothing more than putting those thoughts to paper (well, electronic paper) rather than merely telling someone about what you saw.

For example, today my wife and I were coming out of a Walmart store and as we were getting to our car, I saw one of the Walmart employees going around the parking lot collecting shopping carts.  No, that was not the thing that was unusually although some days when there are more carts in the parking lot than in the store, you may think that.  Actually, the thing that got us upset was that this Walmart employee would pick out the trash left in the cart and throw it on the ground in the parking lot.  In fact, there was one cart that had several empty cardboard boxes, empty shopping bags, packaging material with the item removed and other miscellaneous trash.  He just picked it all out of the card and simply dumped it on the ground.

We probably should have taken a video of it and then sent the video to the store manager at the very least, but alas, I didn’t think of it at the time.  However, this is not the first time that we have seen poor parking lot behavior and I’m sure it will not the be last.

Over the years, I’ve seen people empty their shopping carts and then simply give them a hard shove to push them away from their car.  At least one time, I’ve seen the cart go rolling across the lot until a parked car jumped in front of it to stop it.  Did the person who pushed the cart rush over to parked car to see if it was damaged.  Well, they did jump into their own car and then… drove the other way.

But it is not just about the people who let their shopping carts attempt to fly home.  The funny ones, at least to me, are the people in the gym attire who leave their shopping carts next to their car after emptying them rather than taking them to one of those shopping cart collection areas even though the closest one was less than 30 feet away.  I guess they were just too much in a hurry to get to the gym than to take their cart 30 feet over to the collection area.  I guess that was just too strenuous.

I like the one grocery store, Aldi’s, that makes you put a quarter in a locking box before you can pull a shopping cart out of their storage area.  Does it really cost you a quarter to use a shopping cart?  Well only if you don’t return the cart.  If you return the cart back to the storage area, the quarter pops back out.  You rarely see shopping carts left in the parking lot of this store and when someone leaves their cart in the lot, someone quickly grabs it to take it back to the storage area (or to use it themselves) just to collect the quarter.  I have to wonder if more stores did this whether there would be fewer abandoned shopping carts getting hit by cars.

Of course, as long as we are talking about shopping carts, how about all those shopping carts with wheels that do not roll or are locking pointing a direction different than the one in which you are trying to push the cart.  What about carts with who knows what smeared over the handle bar or that have wheels that are ‘flat’ on one side because someone tried to push the cart outside of the parking lot area and the wheels locked.

But seriously, going back to shopping carts and trash, there is one incident I can think of that tops the Walmart employee picking trash out of a cart and throwing it on the ground.  That incident occurred when I saw a person push the cart along the side of their car.  They then opened the rear side door behind the driver’s seat and started pulling out bags of trash and placing them in the car.  They then push the cart over a little more into the empty spot next to their car, got in their car and left the parking lot.

Ok, that was before 9-11 and the fear of people leaving bombs everywhere.  I’m sure if someone did that today and if the parking lot cameras, which are getting to be more prevalent, got the person on tape, they would soon be visited by representatives from Homeland Security.

I suppose I am a bit unusually in that whenever I go to a store and see a shopping cart near to where we park, I will usually go get it and push it back to the store or at least to the nearest shopping cart collection area.  Imagine a world in which everyone did that and no longer would we have shopping carts damaged by those cart-hating parked cars.  Perhaps that is just a dream.

Anyway, that is how you can take something that you notice as part of your normal daily activity, twist it a bit and turn it into a blog post.  Be observant.  If you see something that you feel you just have to share with someone else, then blog about it instead.

C’ya next time.

Calculating a Moving Average in PowerPivot

Two weeks ago I promised to talk about how to generate a moving average in PowerPivot, but then last week I got sidetracked by telling you about a cool way to display YouTube videos on your SharePoint pages using a web part found on CodePlex that some of my work team members found.  It was so easy to implement, I just had to share it with you all.

However, returning back to the topic of calculating a moving average, the first question might be what is a moving average and then why would you want to use one.  A moving average is simply the sum of two or more time-dependent values in which the sum is then divided by the number of values used.  For example, if I was talking about stock prices, I may want to use something like a 7-day moving average to dampen the effect of individual day spikes or drops in the stock price that are not indicative of the overall stock trend.  (Some long term investors use even longer period moving averages.)  That does not mean that if a stock plummets or soars that I would sit back until the moving average tells me to act.  Any good stock investor will tell you there are many other factors both internal and external to a company that could force your hand to sell or buy any particular stock.  But the point is, and this is the answer to the second question, a moving average dampens randomness so I can more easily see the overall pattern of the numbers that I am tracking.

Ok, so suppose I work for Contoso and wanted to know whether sales are rising, falling or generally flat.  If I look at daily sales, the numbers are likely to fluctuate up and down in no particular pattern preventing me from spotting an overall trend.  The following figure shows Contoso daily Contoso sales over a 3 month period during the summer of 2008.  I chose to show the data as a chart to help show how sales fluctuate by day revealing information that I might not be able to see as easily had I created a table of the same values.

Of course, I could chart an entire year or more, but to see individual days, I would have to widen the chart substantially.  However, even with this smaller time period, I can see that the sales fluctuate quite nicely.  But I might ask are sales increasing, decreasing or staying the same.  If I have a good eye, I might say that sales peak toward the end of July and then fall back a little as the chart moves into August.  But that is not as obvious as the fact that there is a great deal of daily fluctuation.

So how can I visually display trends?  With Moving Average Sales.  Now for the purpose of this illustration, I’m going to create a four-day moving average, but honestly, there is no one right number of periods in a moving average.  In fact, I should experiment with different time periods to see which time period allows me to spot not only overall trends, but also in this case where I am displaying store sales, at seasonal changes.

I already know that if I display data by day, I can use the following formula to calculate the daily sales of just our ‘store’ channel. (Yes, I could simply use [SalesAmount] and apply a channel slicer to use only ‘store’ sales, but let’s stick with the example.)

StoreSales:=CALCULATE(SUM([SalesAmount]),DimChannel[ChannelName]=”Store”)

I can then use this calculated measure to calculate the previous day’s sales for any day by creating the following measure.

StoreSales1DayAgo:=CALCULATE([StoreSales], DATEADD(DimDate[DateKey], -1, day))

You might be able to guess that the formula for calculating sales for two days ago and three days ago respectively are:

StoreSales2DayAgo:=CALCULATE([StoreSales], DATEADD(DimDate[DateKey], -2, day))

StoreSales3DayAgo:=CALCULATE([StoreSales], DATEADD(DimDate[DateKey], -3, day))

With these four values calculated for each day, I can calculate the sum of these values and divide by 4 to get a 4 day moving average using the following calculated value:

FourDayAverage:=([StoreSales] + [StoreSales1DayAgo] + [StoreSales2DayAgo] + [StoreSales3DayAgo]) / 4.0

Now if I switch back to my chart page, I should see that Excel updates the field list to include the new calculated measures.  If I then add the field [FourDayAverage] to the Values box creating a second series in the chart, I now have both the actual daily sales and the four day moving average displayed in the same chart.  The only problem is, I would also want to change the chart format to display the daily sales (my first data series) as columns and my moving average (my second data series) as a line.  When I right click on the chart and select Change Chart Type, I can select Combo as the chart type as shown in the following figure.  In this case, the Clustered Column – Line chart is exactly what I want.  Because I added the moving average series to the Values area last, it becomes by default the Line and all other data series appear as clustered columns.  Since I only have one value for each day, the chart shows an individual column per day.

If I had entered my data series into the Values area in the wrong order, I could simply use this dialog to select the chart type for each series.  When I click OK in this dialog, my chart now looks like the following which more clearly shows the more of the overall trend and less daily fluctuation.

But wait, is there an easier way to do this? Why yes there is.  But to learn how to do that, you will have to wait until next week.

C’ya next time.

The End of the World….Or Not

Someone is always coming up with a new end-of-world scenario.  For several years the most popular one has been the Mayan calendar ending on about December 21, 2012.  Supposedly this scenario was somehow tied to either the reversal of the magnetic poles which would usher in massive tidal waves, earthquakes, volcanos, and more.  Some people even tried to tie the Mayan calendar cycle to the cycle in which our solar system passes through the galactic plane.  Maybe that needs a bit of an explanation.  As the solar system revolves around the Milky Way galaxy, it oscillates between being above the galactic plane and below it.  Looked at on edge, this oscillation almost looks like a wave form.  Now if the galactic plane contains more dust and junk than the area above or below (like the rings of Saturn), going through the galactic plane could pose a greater chance for collision with some of this junk.  Not one to miss a good disaster to make some money just in case the disaster does not happen, Hollywood even came out with a movie with bigger than life special effects that pretty much destroyed the world as we know it.

Not to be outdone, the folks that believe in Planet X (is it Planet IX now that Pluto is no longer considered a planet?) and its periodic appearance in the inner solar system where it creates havoc resurrected the story about Planet Nibiru.  In the last year or so, they have published dozens of videos on YouTube to not only explain about what was going to happen, but to also show videos of the supposed second star of the Nibiru system (if not Nibiru itself).  Of course Nibiru would also create massive earthquakes, flooding, and possibly a pole reversal.  (Doesn’t every good disaster do that?)

They said that Nibiru would come from the south.  Some others said it would come from behind the sun.  Wait a minute, doesn’t that sound contradictory?  The Sun is not over the Antarctic.  Rather it appears above the equator plus or minus about 23 degrees due to the tilt of Earth’s axis.  But they had to explain why millions of amateur astronomers around the world in the Northern hemisphere did not spot something as large as a planet, but they could spot comets crashing into Jupiter a few years ago.

Nibiru is supposedly somewhere in size between the moon and a small brown star.  That’s a pretty wide range.  It is also supposed to pass close to Earth within 5 to 10 Earth diameters.  One video makes the obvious mistake of saying that the Earth’s diameter is 25,000 miles so 10 Earth diameters would be about 250,000 miles or the distance to the moon.  Well half right.  The moon is about 250,000 miles from Earth, but the Earth diameter is only a little under 8,000 miles.  Sorry.  But even if their 10 Earth diameters were correct, an object the size of the moon coming as close as a distance of 80,000 miles should be visible long before it get there.

Maybe it is cloaked?

No, that’s even more ridiculous.

Dark matter?

Anyway, so they then said that it is coming in at a terrific rate of speed.  Ok, that is possible.  After all the speed at which our solar system whips around the Milky Way is 446,400 MPH.  If Nibiru is not travelling with us, but is going in almost the opposite direction, I suppose it could approach fast.  However, at twice the speed we are travelling around the Milky Way, it would take 104 hours or 4 days to travel the distance between our sun and earth.  So taking that extreme case, it is possible to come in very quickly within a few days ‘out of nowhere’ to slam into the earth.  A little more realistic is the fact that we are travelling around the sun at about 69,000 MPH.  Something orbiting the sun in the exact opposite direction would therefore result in about twice the speed on impact.  It would cover the distance between the sun and Earth in about 675 hours or a little over 28 days.

Another video said that Nibiru is being tracked by the radio dish in Puerto Rico.  Unfortunately, that seems unlikely as this radio dish is dug into a bowl shaped depression in the earth and cannot really be moved, especially not to see something coming out of the southern sky.

They also said that it would approach Earth twice as it came through the inner solar system and the exited again, once on August 17th and a second time on September 26th.  Well August 17th has passed and nothing significant happened.  September 26th is just around the corner as I post this, so I would have to ask, ‘Where is this planet?’  I want to see it!’

I suppose September 26th will come and go just like any other day.  Furthermore, December 21st will arrive and we will still wake up to December 22nd to realize that since we survived the Mayan calendar, we have only 3 days left to do last minute shopping for Christmas.  (Although I cannot wait for the going out of business permanently sales on December 20th.)

In all seriousness, there are things out there in the Universe that are bigger than us and could pose a danger to survival on this planet.  Just look at the craters on our Moon, Mars, or any of the other satellites and planets our space missions have been able to take pictures of.  The dinosaurs probably laughed at their scientists who predicted that a huge meteor was heading directly to earth.  The problem is that too many people are hoping for a planetary disaster.  These are probably the same people who like to watch videos of car crashes, building fires, reality TV, and maybe even live footage of riots in Egypt and other Middle East countries (isn’t that another form of reality TV?).

At some time in the future, something out there will take aim at Earth and I only hope that by then we have the intelligence and the will to explore other planets and maybe even nearby star systems so some of mankind will survive.  In the meantime, I expect to be publishing these blog entries for a really long time to come.

C’ya next time…..unless…wait….what is that up in the sky?

By sharepointmike Posted in SciFi

YouTube Videos Within SharePoint?

Do you create YouTube videos?  Would you like to embed them in your SharePoint pages?  Fortunately, there is a very easy solution that allows you to add videos to a SharePoint page so your visitors can view them. For some, this may provide an alternative to storing video files on your SharePoint site itself.  One of the key advantages of using YouTube is that it supports more video file formats that SharePoint and streaming of the video is taken care of by YouTube.  To assist you, let me explain the steps you need to display videos on YouTube on your SharePoint site.

First, you obviously must create your video.  Formats supported by YouTube include:

  •  WebM files—Vp8 video codec and Vorbis Audio Codecs
  •  .MPEG4, 3GPP and MOV files—Typically supporting h264, mpeg4 video codecs, and AAC audio codec
  •  .AVI—Many cameras output this format—typically the video codec is MJPEG and audio is PCM
  •  .MPEGPS—Typically supporting MPEG2 video codec and MP2 audio
  •  .WMV
  •  .FLV—Adobe FLV1 video codec, MP3 audio

YouTube requires a Gmail account to upload videos.  If you do not have a Gmail account or even if you do but want to keep your YouTube activity separated from your other Gmail accounts, simply go to http://www.gmail.com and sign up for a new account.  This article is not really focused on how to create your video or even how to upload your video to YouTube.  However, the following URL links to a video that provides an explanation on how to upload your videos to YouTube:

http://www.youtube.com/watch?v=O7iUiftbKU

To use a video on a SharePoint page, navigate to the YouTube video and click on the ‘Share’ button.  This button is to the immediate lower left of the video to the right of the like/dislike buttons as shown in the image below.

After clicking the Share button, an area appears immediately below the buttons with a short link to your video.  Right click the link to show the menu in the following figure and select the ‘Copy’ command.

With the URL of the video copied to your clipboard, open Notepad and paste it in so you can see the entire URL provided by YouTube.  You do not need the entire URL.  We only need the YouTube ID for the video.  To get only the ID, skip over the first part of the URL that begins with:

http://www.youtube.com/watch?v=

Or it may say:

http://www.youtu.be/

In the first case, the video ID begins immediately after the equal (=) sign and stops before the ampersand (&).  In the second case, it is everything after the final slash.  Because the ID is a combination of upper and lowercase characters and numbers, it can sometimes be difficult to distinguish between a zero (0) and the letter (o).  The difference between a lower case (l) and a number one (1) can also be subtle. Therefore, copy this ID back to the clipboard to use it later rather than struggling to interpret the characters.

Now with the ID code on the clipboard, go to the page where you want to place the video, sign in, and select Site Actions —> Edit Page.

(Note: If Edit Page does not appear at the top of the Site Actions dropdown menu, someone has checked out the page and they must first check it back in before you can continue.)

In the following illustration, we are going to place the video in the Zone 1 Web Part area.  To add a web part to a zone, click in the box: Add a Web Part for the zone you want to use.  You can also place a video in a Page Content area by first clicking in the Page Content area.  Then open the Insert ribbon to display the Web Part control button.  Notice from the image below that the You Tube web part is in the category GilesH Projects.

Oh, I forgot to mention, this YouTube web part is not part of the default SharePoint web part set, but can be found on CodePlex at: http://spyoutube.codeplex.com/releases/view/75322.  The good thing is that you can download not only the WSP file, but also an installer to make it easy to get started.

After adding the You Tube web part, configure it by opening the web part’s menu found on the right side of the title bar when you hover over it with your mouse and selecting Edit Web Part.

There are several parameters you typically want to change in the web part properties including:

¨ Title

¨ Chrome Type

¨ YouTube URL

¨ Video ID

¨ AutoPlay

In the Appearance property group, change the Title to something more appropriately describing the video or you can select ‘None’ under Chrome Type if you don’t want to display a title above the video.

Next open the property grouped named YouTube Properties.  In the first field, you can change the YouTube URL field to include an ‘s’ after ‘http’ if your SharePoint site runs under https.  If your site uses https, you must do this or users will get a message that the page includes unsecure data when they attempt to open the.  Otherwise, leave this address alone.  Then paste the YouTube Video ID that you copied earlier into the Video ID field replacing everything that may be there.  Leave the default settings for width, height, and playlist unchanged for now as shown below.  Of course you can use these properties to resize the video.  If you do not resize the horizontal and vertical size proportionally, part of the video will but cut off when displayed.

Unless you really want the video to begin playing when the visitor opens the page, remove the check in the box by Auto Play in the You Tube Properties group, but leave the Video Start value at 0 (zero) for now.

There are additional properties under the group You Tube Properties as shown in the following figure.  I recommend enabling only the following properties (but feel free to experiment):

¨ Show Player Controls

¨ Auto Hide

¨ Enable Full Screen

¨ Default HD

If the video has annotations, you may also want to check Load Video annotations.

There are other options under You Tube Properties—Accessibility.  However, unless you created closed caption text, you don’t need to enable this option.

Finally, do not make any changes to the properties under You Tube Properties—Developer.  Simply click OK at this point found at the bottom of the properties panel to close the panel.

Your page should now look similar to the following.

Remember to save your page after verifying the its look with the page preview option in the Page ribbon.  Be sure to test whether the video plays when clicked.  If everything checks out, check in your changes, publish and approve the page.

That’s basically all it takes to include YouTube videos on your SharePoint page.  Enjoy.

C’ya next time.

On Being On Time

When was the last time you were on time for a meeting, appointment, or other event?  Or let’s look at it from the other point of view, when was the last time you were waiting for someone who was late and had no idea whether they were even going to show up, if they were delayed or in an accident, or just forgot to meet with you?

It would seem that with the increased use of cell phones and with most of those cell phones having the ability to send text messages that it would be a no-brainer to simply let the other person know that you were running late.  After all, people seem to have time to text and tweet about all sorts of other trivial things like what they were having to eat, the number of times Bob in the office next to them got up to go to the restroom, or the number of times Jill stopped working so she could answer her boyfriend’s most recent call.

Respect for other people’s time seems so yesterday.  If you even bring up the fact that someone is late to a meeting, they given you an attitude as if to say, ‘Well at least I’m busy and getting my job done is more important than this stupid meeting you called.’

But there is another side to this.  If you work in an IT position at a company, I bet you receive your share of unsolicited phone calls and e-mails asking you, ‘When would be the best time that we can meet so I can show you our new Thing-a-ma-bob’ that will change the productivity of your office.’  What makes these calls just plain rude is that you never contacted them for a meeting, they just assume that you want one.  They don’t even ask whether you would like to meet to learn more about their product.  I have to admit, I generally don’t even respond back to many of these e-mails and calls.  I treat them sort of like spam.  Responding to them only confirms that the e-mail address or phone number is valid.

And while I’m talking about phone calls, how annoying are they when you are busy trying to develop a new program, analyze a set of data, or plan out a project schedule? There are many times that I am not in my office being either at a meeting or working directly with one of my direct reports.  Even when I am in my office, I will not interrupt my concentration on a project or a meeting with a staff member for a phone call in which the caller is not known to me.  One of the toughest things for a programmer, writer or manager can do is to get into the ‘zone’.  Any interruption from that zone costs not only the time of the interruption, but an equal amount of time trying to refocus your thoughts to get back into the zone.

So what do you do?  My suggestion is that you allocate time during the day to respond to email and to return phone calls.  The rest of the time, you may want to isolate yourself so you can focus on getting your job.  For example, I answer most emails either first thing in the morning when I get into work, right after lunch, or during the last half hour before I go home.  During the rest of the day, I monitor e-mails and only respond to true emergencies.

I treat phone calls the same way.  If I am with a staff member or in focused thought and if the caller is not the boss, I’ll let the call go to voice mail to be reviewed later when I finish the task or when the staff member leaves.  It is amazing how many callers do not leave a message.  I maintain that if the call is important enough, the caller will leave me a message.  Often times, by leaving a message, I can research the question and respond back to the caller more effectively when I review the calls later.  Maybe I’ve missed that call offering me $100,000 a year for life from Publisher’s Clearing House, but I’ll take that risk.

I treat meeting requests the same way.  If it is a meeting that I or a member of my team must attend because it affects one of our projects, I make sure one of us can be there.  If something happens that we cannot attend, I notify the meeting organizer.  I don’t let them just wonder whether we are going to show up or not.  If the meeting does seem to really affect our team or if we are already busy with other meetings or tasks during that time and cannot attend the meeting, I’ll reject it right away and if necessary ask the organizer to check my or one of my staff’s calendar in Outlook for free time to reschedule it.  Yes, sometimes emergency meetings might have a priority, but then I feel responsible to notify the organizer of the first meeting that I or my staff cannot attend due to other priorities that have come up and that we need to reschedule.  Never, never leave a meeting organizer not knowing if you will attend or not.

I know some people seem afraid to reject a meeting even when they have three meetings scheduled for the same time and they know that they cannot attend all three.  They seem to think that rejecting a meeting is something bad like not being a team player.  I think that accepting all of the meetings knowing you do not intend to attend them is just plain bad office etiquette.

Finally, if you commit to going to a meeting, get there on time.  If a prior meeting is not over, ask if they can schedule a follow-up time to continue the discussion if necessary because the meeting time has ended and you need to be somewhere else.

Oh, and this does not apply just to work.  Common courtesy like showing up on time or notifying the other party that you will be late along with an estimate of when (or if) you will get there will get you more respect than trying to show how important you are because you have more meetings than you can attend.

Maybe some other time I talk about the challenge of getting repairmen or deliveries to your house when they say they will be there.  Ever get a repairman to tell you that they be at your house sometime between 8 AM and 4 PM?  Do you think that could be narrowed down a bit?  And again, a phone call when you are late would sure help.

C’ya next time.

Total It Up

Sometimes in a report, you need a running total of an amount, not just the daily totals.  In the previous pivot tables, I have shown reports like the one in the following figure which displays data by date. (This week I return to using the Contoso 2012 data if you want to follow along.)  Note that I have chosen to show the subtotals for the month at the bottom of the group only and I use a sort by column to sort the months in chronological order rather than alphabetical order.  I have covered these techniques in previous blog entries.

This report accurately portrays the sales by each day of the month.  However, suppose I want to see the cumulative sales within the month.  In other words, on January 1, 2007 I want to see 6,085,839.18, but on January 2, 2007, I want to see 12,356,496.35 which is the sum of sales for January 1 and January 2.

There is a way to do this using the DAX time function, TOTALMTD that stands for Total Month-To-Date.  The basic syntax for this function is:

TOTALMTD(<expression>,<dates>[,<filter>])

I can use this function to calculate a new measure in the FactSales table since that table contains the SalesAmount of each sale.  The expression I will use is:

SUM([SalesAmount])

This DAX function sums the Sales Amount column using the dates provided in the second parameter.  For this example, the date column I point to is DimDate[DateKey] which is related to the DateKey column in the FactSales table.  Why not just use the DateKey column in FactSales?  The column that is used to provide dates must have unique dates in it and DateKey in FactSales is not unique.  However, it is unique in DimDate.

Another interesting point is that the date column selected for the TOTALMTD function will not work properly if the row definition is based on DateKey from FactSales and TOTALMTD uses the date column DimDates[DateKey].

In any case, my final measure expression is:

MonthToDate:=TOTALMTD(SUM([SalesAmount]),DimDate[DateKey])

After formatting my data as currency, I now have a column with the daily sales and a column that shows the sales to date for the current month.

There are similar functions for calculate totals by quarter and by year.  They are TOTALQTD and TOTALYTD respectively.  The only difference between these functions is on which date they choose to reset the total back to zero.

Another interesting function is the PARALLELPERIOD function that I can use to retrieve a set of dates from a different time interval from the current date.  If all I want is to compare sales for the current month to the previous month, I could use the PREVIOUSMONTH function in an expression such as:

PriorMonthSales:=CALCULATE(SUM([SalesAmount]),PREVIOUSMONTH(DimDate[DateKey]))

But what if I wanted to get the sales from three months previous.  On method would be to nest PREVIOUSMONTH functions like this:

PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(DimDate[DateKey])))

However, that expression is not only hard to read, it is far too easy to make a mistake matching parenthesis correctly.  A simpler function too use is the PARALLELPERIOD function.  This function allows me to define a previous period such as three months ago using the much simpler expression:

PARALLELPERIOD(DimDate[DateKey],-3,month)

With this function, the first parameter is still the date column from my dimension table.  The second parameter is the number of intervals I want to offset my calculation.  In this case, because I want to calculate the totals for three months ago, I use a negative value, -3.  The third parameter defines the type of period I want to use and includes values ‘month’, ‘quarter’, ‘year’.  Unfortunately you cannot use this function to calculate the sales from three days ago.  This is a topic I’ll take up next time.

That’s it for this time.  Next time I’ll take a look at some ways to calculate running averages.

You Want Me to Talk to an Inanimate Object. Really?

One of the emails I receive daily has a ‘Geek Trivia’ question.  The site is: http://www.howtogeek.com if you want to check it out.  I find the questions always interesting even when I get the answer wrong.  Today’s question asked for the name of the trick in programming where you debug your code by explaining your code to an inanimate object.

The correct answer was Rubber Duck Debugging.  Honestly I’ve never heard the term before.  Basically, it requires you to take a moment after finishing your coding to explain your code out loud line by line to an inanimate object. (The article does not say whether this occurs at the end of a module, project, or solution, but I’m thinking that the more often the better because even a rubber duck has its limits on boredom.)

As silly as that may seem, I realized that this technique has at least a grain or two of truth.  Back when I did more programming, (some may say that was back in the days of 2-character BASIC variable names), explaining how my code worked to someone else often revealed something I missed.   Of course I was careful to ‘gloss’ over any problems and most of the time the other person never knew.  But it did help me to identify problems and to go back to the code later and amend my code.

More recently I’ve applied this principle to everything I write.  I can tell you from painful experience that re-reading any text to myself after writing it is not as effective at finding spelling errors, grammar errors, and just plain contextual problems as when I read the text out loud. Of course, most of the time I do not read the text to another person.  At home, I generally read the text to my pet cockatiel.  I can tell you that this bird is one of the most informed birds around…if it only understands a portion of what I’ve told it.  But at work when my cockatiel is not available, I still mouth the words in a bit of a whisper.  Anything less does not seem to work as well.  At one time I worried that people would think that I was talking to myself.  But with everyone having cell phones and Bluetooth connections so they can talk hands free, my worries of appearing insane are gone and I’m free to talk to myself anytime day or night.

So give it a chance.  If you can find someone to whom you can talk through each line of you code, great!  If they also understand how to code, even better.  Who knows? They might see something you missed.  Even if you have to talk to yourself, try it.  If not out loud, at least as a whisper.

C’ya next time, and that whispering you hear might not be your workmates talking about you, but rather it may just be the person in the next cubicle reviewing their code or report.

…or not.

Statistics: PowerPivot Style, Part 4: The RANKX Function

Last time I showed you how to sort the students within a grade by their total scores.  I showed that you can sort by any column displayed in the pivot table using the Sort dialog of the Student Name row labels.  This method requires that I include the Sum of StudentScore measure in the pivot table.  I cannot sort by a column not included in the pivot table.  I also explained that you could use the new Sort by Column feature to define the sort order.  The advantage of this method is that you do not have to include the sort by column in the final pivot table.  The problem with this method is the inability to define whether the sort is ascending or descending (or at least I have not yet found it.  If you have please add it to the comments section.)  There is one other way that I’ll cover today to sort the students by their scores.  That is by using the RANKX function.  But first, here again as a reference is the list of students and their total student score calculated last time.

 

The first parameter must either be a table or be a DAX function that returns a table.  In this example, since I want to calculate the student’s rank across all classes, I choose to use the expression ALL(Students).  This expression returns a table that represents all of the students in the school, not the student represented by the specific row where I want to do the calculation.

You might question why I use a function that returns all students in the school and not a function that would return all students in a class.  First, you have to think about the fact that the order of students in any grade when ranked by all students in the school will be the same order of students if the table only included students within a specific grade as long as all of the classes in the grade have the same total possible point score.  The advantage of using the ALL(Students) expression means that I can use this expression in a pivot table that uses slicers to show different grade levels without affecting the calculation expression.  Otherwise, I would need separate student rank expressions for each grade level.

Suppose you have the following grades for only 6 students and then rank the student scores across all students.

Student

Total Score

Grade

Rank

Student 01 632 Grade 1 2
Student 02 583 Grade 1 5
Student 03 599 Grade 2 4
Student 04 654 Grade 2 1
Student 05 610 Grade 1 3
Student 06 512 Grade 2 6

 

Now if you only wanted to look at the second grade students, you could filter on this table to get:

Student

Total Score

Grade

Rank

Student 03 599 Grade 2 4
Student 04 654 Grade 2 1
Student 06 512 Grade 2 6

 

As you can see, the order of students defined from the highest score to the lowest for second graders is still Student 04, Student 03, and finally Student 06.  This is true even though there is no second grade student with a rank of 2, 3, or 5.  Therefore, ranking all of the students and then filtering by grade gives us the same student order as filtering by grade and then ranking the selected students.

The RANKX function lets you define a DAX expression that it can evaluate to a scalar value for each row in the designated table.  The thing that is special about this is that the expression can reference data in a related table.  In fact, I will use this capability in a moment, but first, let me explore the other features of the RANKX function.

The third parameter lets you supply a value and determine the rank of that value based on the entire list of values.    I’ll come back to this later after I calculate the student rank to show how this works.

The fourth parameter let you define the direction of the sort order.  By default, if this value is not entered or if a value of zero (0) is provided as the fourth parameter, the scores will be ranked in descending order.  In other words, the highest scores will be at the top which is what I saw in the Sort by Column feature.  While this may be exactly what I want here, I could also provide a value of one (1) for this parameter to sort the scores in ascending order with the lowest score at the top.

The fifth parameter lets me decide how to deal with ties.  By default, all rows with the same value will have the same rank.  Let’s suppose I had 1 student with the total score of 635 giving them a rank of 1 and 4 students with the total score of 632.  Each of these four students would have the rank 2.  However, the next student with a score of 631 would have the rank 6.  The values 3, 4, and 5 are skipped because these three students are tied with the student in the rank 2 and there is no way to position any one of these students ahead or any other.  You can also supply a value of ‘Skip’ for this parameter.

On the other hand, if we specify a value of ‘Dense’, the student with a score of 635 is still ranked ‘1’, each of the next 4 students with a score of 632 are ranked ‘2’, but the next student with a score of 631 is ranked ‘3’.  There is no skipping of ranked values.

Finally, parameters 2 through 5 can be repeated to defined a hierarchy of ranking students that can be used to break ties by using other fields (expressions).

So returning to my example, want to use the following expression to rank my students in descending order by their total score.

=RANKX(ALL(Students),SUMX(RELATEDTABLE(Assignments3),Assignments3[AssignmentScore]))

This gives me the result in the following figure.

To rank my students, I can now select the StudentName column and click on the Sort by Column button in the Sort and Filter group of the Home tab to define my StudentRank column to determine the sort order of the student names.  Moving to the Excel pivot table, I can display just the student names without any other numeric data and the student names will appear in descending sorted order by their total scores.  Furthermore, by adding a grade slicer, I can see the students ranked by grade as shown in this figure.

 

As a final word on this topic, suppose I wanted to find out what rank a student would be if they had a score of 584.  I could created a calculated value in the calculation area with this expression:

TestRank:=RANKX(ALL(Students), SUMX(RELATEDTABLE(Assignments3), 
             Assignments3[AssignmentScore]),584)

In the following figure, you can see that I have a StudentScore value of 585 with a rank of 53 in a descending sort.  Since a score of 584 is lower than 585, I would expect the rank to be after 53 and as you can see in the figure, the calculated value of 54 shows this to be true.

Well, I suppose that is enough for today.  C’ya next time as I start to take a look at some of the date functions in DAX.