Monthly Archives: May 2014

Returning the First Temperature in PowerPivot

Here in Southeast Texas, it has been rather cool this year. In fact, as of this writing (May 25), there has not been a day yet at 90°F or above. I thought that this was pretty unusual, but I could not (easily) find a temperature dataset on the Internet to prove it. However, I thought that I had such a dataset stored away somewhere in my computer, but I could not find it (due to poor file naming) until this weekend. It was missing a few years of data in the middle of the dataset, but I had enough to test out my theory After I loaded the data into PowerPivot, it was pretty easy to come up with a DAX measure that would give me the first day of the year at 90°F or above.

First90:=CALCULATE(MIN(Table1[Date]),Table1[Max TemperatureF]>=90)

Here is a picture of the pivot table with that measure.


If you closely you can see the years that are missing data. Well, since that went so smoothly, I figured why not look up the first day of freezing for each year. So, my first attempt used the following formula:

First32:=CALCULATE(MIN(Table1[Date]),Table1[Min TemperatureF]<=32,MONTH(Table1[Date])>6)

I added the criteria MONTH(Table1[Date])>6 to look up only the first freezing temperature that was at the end of the year. The results are shown below:


Now, the missing data was easy to see (1979-1985), since no result was returned for the First90 measure and a bogus result was retuned for the First32 measure. However, some of the legitimate data also showed no result for the First32 measure (1986, 1987, 1994, and 2002). I had failed to realize that the first freeze of the winter may not be in the year where the winter started. In order to find a solution that did work the way I intended, to would have to take into account that the first freeze of the winter season could also be in the next calendar year.

I am not going to bore you with the details of the numerous times that my attempts to solve this problem ended up in failure. Also, I would not want to bias your attempts to come up with a better solution than I have, since I am sure that there is another one out there. The key to the solution is that I needed to create a custom time period to call “winter”, and it had to work in a pivot table with a year row field. Here is my solution:

I created two calculated fields, one for the dates at the beginning of the winter and one for the dates at the end of the winter. Realize that these may not work for datasets from anywhere in the world (and especially in the Southern Hemisphere).



I then created another calculated column for the desired DAX formula (I could not get this to work as a measure, so feel free to extend this).

[FirstFreeze]=CALCULATE(MIN(Table1[Date]),ALL(Table1),Table1[Min TemperatureF]<=32,DATESBETWEEN(Table1[Date],Table1[BeginYear],Table1[EndYear]))

The results in the [FirstFreeze] column give a result for every day with valid data, so the field setting has to be set to Average to return the desired value. Further, this field cannot be aggregated as Average in the pivot table when the calculated column has a Date datatype, so in order to use it must be changed to decimal type in the DataModel and then back to Date by use the filed settings in the pivot table (hope that is clear) J.

When done, the resulting pivot table looks like this:


Now that you can see that the years that were missing the first freezing temperatures in the initial attempt now have populated.


David Hager

Interesting job postings related to Power BI and VBA

After I wrote about my opinion on Excel and Power BI, I decided to search trends at related to Power BI and VBA. First, I put in the search terms Excel, VBA, and PowerPivot. Interestingly, the following shows an unexpected (to me) result.

Prior, to mid-July 2013, there were basically zero job postings based on those criteria. Then, the job postings grew significantly from that point to now. Obviously, this was long after the introduction of PowerPivot (~4 years), so what happened at this time? The following link is to a posting on the SQL Server blog on July 8, 2013.

This was the announcement of the introduction of Power BI. So, why would this announcement causing a “surge” of job postings base on searching for Excel, VBA, and PowerPivot? It is clear (to me) that there was(is) an expectation by the corporate world that Microsoft would implement a programming language (probably VBA) that would eventually be extended throughout PowerPivot (the DataModel object so far) and the rest of the Power BI offerings, creating the ability to build corporate, non-manual solutions using Excel as the hub.

I will be watching closely to see if this “indicator” gives any clues pertaining to the strengthening or weakening of this expectation.

My opinion on Excel and Power BI

If I were Microsoft, I would have a plan for the future for Excel and Power BI, and I would be telling everyone about it. This would be my plan:

1) There would be a basic form of Excel. It would NOT have any add-ins to enable. It would not have any enhancements beyond what exists today. It would work on any device. It would NOT include the Data Model. The new Office apps would work seamlessly with it. Without having any hard statistics, I am guessing that > 98% of Excel users outside of a work environment would be extremely happy with this product.

2) Office 365 – no changes – mainly for corporate use.

3) The new product would be Power Excel. Excel as it currently exists would be integrated with the new Power BI tools. The VBA object model would be expanded so that all of the BI “components” would be fully programmable/automatable inside SharePoint and with other Office products. It could toggle between SharePoint installed on corporate systems and SharePoint in the cloud. An app on any device could access and run Power Excel loaded on a server. The argument against VBA for enterprise applications has always been that the code was not secure and not compiled. Code in Power Excel files would be “protected” by SharePoint, thus making an outside programming solution unnecessary.

So, I would do this and make sure that my customers knew the details of my long-term plan so that they could make timely business decisions based on that information.

It’s probably obvious based on my comments here that I have no inside knowledge of what Microsoft intends to do in the future, but it is my hope that their vision is similar to this.