Often users want to import multiple identical Excel files into PowerPivot as one table. Unfortunately PowerPivot does not have native support for this.
The good thing is that there is a nice work around which someone else already blogged about. You can find out how to do it here: PowerPivot from Identical Structure Excel Files
Last week I head the pleasure of attending TechEd North America in Atlanta, both presenting and meeting a lot of our customers. Teched was our great coming out party for the Analysis services Denali release. In this post you can find a lot of references to TechEd sessions and relevant blog posts.
The most important thing to come out of Atlanta is the updated vision and roadmap for the Analysis services team:
Watch the session here, this also includes a general overview of what is new for SSAS in Denali. The corresponding SSAS team blog post here and another interesting one from Chris Webb.
Other interesting sessions are:
)
And of course a awesome Crescent session:
Also make sure to check out the BI Power Hour: BI Power Hour which is always fun with demos of the latest technology. More detailed blog post will become available when Denali CTP3 becomes available.
If you want to know when CTP3 for SQL Server Denali (including PowerPivot and SSAS) will become available you can subscribe to a notification here.
I had an interesting question on my ask a questions page, one that I have (or something similar) heard a few times before. So I decided to do make a blog post on how to solve it
Our company tracks changes in the resource planning using a data warehouse. An example would be:
Assignment 1: Ian 8 hours planned for 3-Jun; valid from 1-jan-2011 until 1-feb-2011.
Assignment 1: Ian 6 hours planned for 3-Jun; valid from 2-feb-2011 until 15-feb-2011.
Assignment 1: Ian 7 hours planned for 3-jun; valid from 16-feb-2011 until infinity.I what to enable our end-user to select any date level (Date, Week, Month, year) and get the current planning for that planned date (3-jun).
First of all I created a table in Excel to represent the data:
Next I create a date table that I can use to select dates at different levels using a slicer:
I did not create a relationship between the two tables because I want to use the values of the date table to search for values in the planning table. So far i have prepared the tables, next is creating the report.
First I created a workbook and a new pivottable with the slicers from the date table and put the employee name on rows:
now for the interesting part, on to the DAX formula. We want to use the values from the slicers to determine the date we want to search the planning table on. We can use values(columnname) to get the current value of the slicer and create a date from that on the fly. For example values(‘Date’[year]) will get the current value of years from the slicer. IF you join all 3 together you can create a new Date: Date(values(‘Date’[year]),values(‘Date’[Month]),values(‘Date’[Day]))
Slicers can have multiple values selected at the same time so we need to make sure that only one is selected at the same time, otherwhise we cannot create this date. We can do this with the following formula’s:
=if( countrows(values(‘Date’[year])) = 1 &&countrows(values(‘Date’[Month])) = 1 &&countrows(values(‘Date’[Day])) = 1,
Now on to creating the DAX formula to finding the right values in the planning table:
=if( countrows(values('Date'[year])) = 1
&&
countrows(values('Date'[Month])) = 1
&&
countrows(values('Date'[Day])) = 1,
CALCULATE(sum(Rates[Hours]),
FILTER(Rates,
Rates[Startdate] <= Date(values('Date'[year]),values('Date'[Month]),values('Date'[Day]))
&&
(Rates[Enddate] >= Date(values('Date'[year]),values('Date'[Month]),values('Date'[Day]))
|| ISBLANK(Rates[Enddate]))
)
)
,Blank()
)
Let’s look at this step by step:
First we want to calculate the sum of Hours.
CALCULATE (sum(Rates[Hours]),
for the rows in the selected date range:
FILTER(Rates,
Rates[Startdate] <= Date(values(‘Date’[year]),values(‘Date’[Month]),values(‘Date’[Day]))
&&
(Rates[Enddate] >= Date(values(‘Date’[year]),values(‘Date’[Month]),values(‘Date’[Day])) || ISBLANK(Rates[Enddate])) )
Filter the Rates table to return only the rows where
the Rates[Startdate] <= the selected date
AND
Rates[Enddate] >=the selected date OR the Rates[Enddate] is empty.
When this formula is used we get the value we wanted based on the slicer we selected:
Make sure you turn the following “Visually indicate items with no data” for each slicer (right mouse click, slicer settings):
Another blog post with a similar question and answer you can find here: http://www.powerpivotblog.nl/get-values-within-a-start-and-end-date-using-powerpivot-dax
I’m very excited that I will be presenting a session on what is new in DAX for SQL 11 on TechEd 2011 this year:
Data Analysis Expressions (DAX) is a new calculation language introduced in Microsoft SQL Server 2008 R2 within PowerPivot. DAX is an extension to the Excel formula syntax and offers information workers an intuitive yet powerful tool to add business logic to their PowerPivot applications. In SQL Server code-named “Denali”, with the introduction of the BI Semantic Model, DAX becomes a key component of any Corporate Business Intelligence application built on the Microsoft Business Intelligence stack. The goal of this session is to introduce you to the concepts in DAX, how to add calculated columns and measure to your model, and the new DAX features coming in “Denali”.
You can also find me at the Self Service BI booth in the exhibit area.
Of course there will be several colleagues that will do other very interesting session on the new design tools, general overview of Denali and of course Crescent (+way more).
Check out all the sessions here.
Hope to see you there!
Again a interesting question on my ask a questions page, keep them coming :)
Luke had a question where he wanted to get a date based calculation:
I have a table of people and a table of project assignments.
The assignments have a person, start date, end date and number of hours per week.I want to create a pivot that has the date as columns and people as rows, with the total number of assigned hours as values.
I created a table in Excel that recreates this scenario:
Next I created a new table that contains a separate date range:
I loaded both tables into PowerPivot. Because I want to get the total number of hours per day I am unable to work with a relationship between the date table and the fact table. I need to create a formula that searches the fact table for those that rows are appropriate for each date value in the pivottable.
=Calculate( sum(Project[Hours]), FILTER(Project, COUNTROWS(FILTER(VALUES(Dates[Dates]), Project[startdate] <= Dates[Dates] && Project[enddate] >= Dates[Dates] )) > 0) )
This measure does the following:
Calculate the sum of project[hours] for those rows in the Project table that has more than 0 rows in the Date table where Project[startdate] < = ‘Date’[Date] and Project[enddate] >= ‘Date’[Date].
The only downside of this query has to iterate through the entire project table for each row in the date table, is if the Project table is huge this might have big performance impact.
A second question was to create a project start date to current date sum. Again we need to create a measure to do it:
=if(COUNTROWS(VALUES(Dates[dates])) = 1, SUMX(DATESBETWEEN(Dates[dates], FIRSTDATE(Project[StartDate]), VALUES(Dates[dates])) , Project[SumPerDayMeasure]) , BLANK())
What happens here is that we want to sum all the values of the measure per day for each day from the beginning of the project to the current day.
Sumx will sum the values of our previous measure for each day between FIRSTDATE(Project[StartDate]) and VALUES(Dates[dates]), being the current day in the context.
Got an interesting question today. The question was about creating sparklines for all the country’s that shows a running total of sales for the last 12 months (Rolling 12 months) based on the year that I select from a slicer. I use the trick I blogged about before to work with sparklines and PowerPivot, I would recommend reading it for some background.
First I want to start by creating the sheet that I want to use as a report. On this sheet I start by creating the slicer that I want to use, In this case Calendaryear:
Next I create a second sheet where I want to collect my data for the sparklines.
Because we want to show the running 12 months per year I would like to use only the months of one year that is the baseline of the running month. So we hook up the slicer from our report to the pivottable in the other sheet. Select the slicer, go to options and click on pivottable connections, here I can select the pivottable from the other sheet to be filtered as well:
Next we want to create a measure that creates our running total:
=if(COUNTROWS(values(DimDate[CalendarYear])) = 1, CALCULATE(SUM(FactSales[SalesAmount]), DATESBETWEEN(DimDate[Datekey], DATEADD(FIRSTDATE(DimDate[Datekey]),-11, MONTH) , LASTDATE(DimDate[Datekey]))) ,blank())
What this DAX measure does is first of all check if only one year is selected using:
COUNTROWS(values(DimDate[CalendarYear])) = 1,
If more than one year is selected we return blank(). If only one year is selected we calculate the running total by doing a sum of FactSales[SalesAmount] by creating a date range using DatesBetween.
The start date of the range is determined by getting the first day of the current month (we know this because of the current row context) and then go back 11 months before that:
DATEADD(FIRSTDATE(DimDate[Datekey]),-11, MONTH)
to the last date of the current month:
LASTDATE(DimDate[Datekey])
Using this in combination of calculate the sum will now calculate over the entire date range.
Now we have the data set up we can create the sparklines. First we need to copy the rows and paste them to the report and create the sparklines on the report page, check the previous blog post on how to create the sparklines.
I have uploaded the workbook for download here.
Check out this great video from the Office team on what Excel and PowerPivot can do for you and your organisation:
You can find the original video here.
I got an excellent question last week on the ask my answers page that brought me new understanding of DAX. So finally a new interesting (I hope) blog post on DAX.
Let’s say I have a set of sales per week of a specific brand:
Now I want to have the average of sales per week (slicable by brand) and compare it to the average of the last 3 weekly totals. I prepare the pivottable :
