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).
[BeginYear]=DATE(YEAR(Table1[Date]),7,1)
[EndYear]=DATE(YEAR(Table1[Date])+1,6,30)
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.
HTH!
David Hager
Excel For You 