Use slicer values in a calculation with PowerPivot DAX

June 17, 2010

By Kasper de Jonge

A while ago i had a question on my ask a question page, Sasha wanted to do the following:

How to define the date I want to see as “open items”.

E.g. Open Items by 25.05.2010
Item 1: Posting Date: 20.05.2010 Clearingdate: 28.05.2010 Value 100 EUR
Item 2: Posting Date: 22.05.2010 Clearingdate: 27.05.2010 Value 200 EUR
Item 3: Posting Date: 23.05.2010 Clearingdate: 24.05.2010 Value 300 EUR

Result will be 300 EUR (Item 1 + 2) Postingdate 25.05.2010 OR Clearingdate = 00.00.0000).

But how to select the 25.05.2010 for this calculation. I have not a date like 25.05.2010 in my Pivottable?

We have two challenges here:

  1. Create a between like measure using dax
  2. Get values from a slicer to be used inside this calculation, the data from this slicer mustn’t effect the data inside the pivottable

I have created the following simplified scenario to recreate sasha’s question. I started with a dataset:

I want to get the sum of amount where my slicer is between values a and b. The first thing i want to solve is how to get a value from a slicer inside my calculation.

To do this i created a new dataset with the values i wanted to use in my slicer, since i might want to use a value that is not inside my powerpivot data. I loaded this into PowerPivot:

This made sure of two things: i have the data i want inside a slicer and because i didn’t create a relationship between this data and the fact table nothing will happen when i select data from the slicer.

Because the data from the slicer isn’t connected to my main fact table doesn’t mean we cannot get data from it. The slicer will make sure the table i just loaded will be “sliced” to the value we want.

When we create the pivottable we seen the following:

Because we didn’t create a relationship PowerPivot keeps reminding us a relationship might be needed, because this is pretty annoying we can turn this off by clicking on the Detection button:

So we are good to go, to get the value of the slicer we can now do sum(aantal[aantal]) in our measure. A measure that uses the value from the slicer would be:

Slicer measure=if(COUNTROWS(aantal) = 1, sum(aantal[aantal]), BLANK())

This will check if we have only one value selected, and if that is the case return the sum of the column. This results in the single value because the table has one row (it is sliced to one row). Otherwise return BLANK(). This will look like:

We now have the value of the slicer in our pivottable and we can use it to create a measure that will give us the sum of amount where the value of our slicer is between a and b.

What we are going to do use sumx to sum Table1[amount] over a filtered table, this table will filter the values where the value of slicer aantal between column a and b. This will look like:

=IF(COUNTROWS(aantal) = 1 ,
			SUMX(FILTER(Table1,
				Table1[a] <= sum(aantal[aantal])
				&& sum(aantal[aantal]) <= Table1[b]
			)
			,Table1[amount])
	, BLANK())

step by step:

  • Check if we have selected only one value in our slicer, if more return blank
  • Do a sum of Table1[amount] over a filtered table, more on sumx at this blog post of Rob Collie.
  • Use filter to return a dataset of specific values from a table, in this case return all rows where value of column a <= value of the slicer and value of the slicer <= value of column b

This will look like this in our Pivottable, as we can see we only have the rows where our slicer measure is between a and b:

When we remove a and b from the pivottable we see the total sum:

This again shows you the amazing power of DAX, a lot is possible. But a word of caution is at his place, SUMX and FILTER are two of the most CPU consuming functions in PowerPivot, it creates a new dynamic in memory table for every cell in the pivot where FILTER is used. I tried a similar function at a Pivottable based on Contoso and it took me a lot of CPU. Check out this blog post of Rob Collie on the use of filter. SUMX and FILTER are amazing giving you all kinds of possibility’s but you need to think where to use what as you can read in Rob’s blog post.

Leave a Comment » | DAX | Permalink
Posted by Kasper de Jonge

Welcome Query designers!

June 14, 2010

 
-Guest Post by the one and only Dany Hoter !

We Excel geeks aren’t spoiled with fancy query builders. We have had MSQuery for the last what? 15 years?

Suffice to say it is a tool from the class of Access 1.0.

For those of you not familiar with it’s looks here it is:

msquery1

Not only it looks very old but on the technical side it only supports SQL features which existed somewhere in the late 80’s or early 90’s.

Enters PowerPivot

If you followed the posts on this blog and others about PowerPivot you heard a lot about DAX calculations and creating relationships between tables and about using the tables in PP to create nice looking presentations.

What you haven’t heard so much about are the query designers. Maybe it is because this feature doesn’t look so special for someone coming for the world of SQL Server Reporting Services (SSRS)  and SQL Server Integration Services or just plain Visual Studio development. For us the lowly Excel users the possibility to use a decent query designer for creating complex SQL queries in a drag and drop  fashion is a very significant improvement.

Not only we received a query designer but we actually have 2 , one for SQL Server and one for Analysis Services. (Notice the specific mentioning of SQL Server and not just any relational database, not even Access (Sigh…)

Using the SQL server Query designer

So you want to bring data from some tables in SQL Server. You connect and choose the option to write a query that will specify the data to import and not the option to select tables. This is what you see:

image

Is it the time to remember your (extinct (?)) SQL skills or maybe paste a SQL statement you generated in SQL enterprise management?

This is what I thought too and I actually did paste SQL syntax generated by a foreign tool (Business objects but please do not tell anyone about it).

Only later I noticed the button on the bottom right saying “Design” and gave it a try with very low expectations.

The main protagonist appears:

msquery2

(The red frames are added by the special effects department)

Table Relationships (AKA joins)

After selecting a few tables we get this:

image The relationships were discovered automatically by the designer based on constraints in the database.

If the auto detected relationships are not enough or needed to be changed you can unselect the auto detect option and click Edit fields or on the icon for add relationships. For the example in the case of the Adventure works database there are three date fields in the Fact table related to the DimDate table. The auto detected relationships for the DimDate table look like this:

image

This settings will result in 0 rows retrieved because it requires that all three dates will be the same which is very unlikely.  You can delete 2 out of 3 relations and chose the Date dimension that is best. You can also edit the SQL syntax and join the DimDate a second time. I couldn’t find a way to do that in the graphical designer. Notice that once you edit the graphically designed query, you can’t go back because the wizard will discard your query and you’ll have to start over.

Filters

image

Notice that the option for parameters is not implemented in this release.

A nice touch is the option for creating multiple choices by clicking on the + sign in the edit control. Unfortunately there is no list of values and you have to know the value you want to filter on.

At any point you can run the query and see the results.

Aggregate query

image

You can generate aggregate queries by clicking the Group and Aggregate button and changing selecting some fields for the aggregation. Any other field in the query will automatically be part of the group by.

Entering the designer again

From the PowerPivot menu you can use table properties to enter the designer again.

Using the analysis services designer

Before we see the AS or MDX query designer we should ask ourselves why do we need to bring data from a cube into PowerPivot.

Aren’t we making a multidimensional object into a flat table just to make it again a multi dimensional object ?

At this point I’ll give just two reasons why this exercise can be useful.

  • Bringing data from two or more separate cube on potentially more than one server.
  • Combining data in a cube from data not (yet?) in the cube from a SQL table or from a table in Excel. The last option I found very powerful and have used it in production already(A good topic for another post)

Using the designer

image

Looks familiar ah? Same principle here, first try to your MDX skills and only then after some frustration notice the design button and give it a try. I’ll risk being obnoxious here:

PowerPivot team – first use the graphic designer and only from there allow text editing

I hope the message is clear enough :)

image

From here you are basically in Drag&Drop heaven. You can drag measures, fields , whole hierarchies , a whole dimension etc.

Filtering experience is very friendly indeed:

image

You have lists of values and hierarchy navigation while choosing values.

You can even create new calculated values but I’m afraid that one can get lost with all the different options of creating calculations in the server cube,  as part of the query , in the table once in PowerPivot , in the pivot table :)

One important word of caution about calculations. The calculated members from the cube will be transformed to regular fields with fixed values once brought into the PP environment. Any dynamic nature they have will be lost.

Another word of caution is about security.

Unless you publish the PowerPivot with data open for everyone and force the user to refresh the query using her own credentials, you risk breaching cube security. If you query using the credentials of a user who can see all the cube and give other users the right to open the Excel workbook, they are going to see all data until they attempt to refresh. This is not different from sharing a static report or a regular Excel pivot that does not refresh on open.

Summary

These two designers are a reason enough to start using PowerPivot , don’t you agree?

1 Comment | Data Import | Tagged: | Permalink
Posted by danyhoter

PowerPivot observations from TechEd/BI Conf, Pt 1

June 11, 2010

 
Kasper and Rob in The Big Easy

 

“Ouch”

-Rob’s feet, knees, brain, and liver

Hi folks.  On my way back from the MS BI Conference (and TechEd) in New Orleans.  Had a great time, almost too much to report.  Here’s a sample:

  1. Met Kasper and Denny for the first time!
  2. Met a bunch of other PowerPivot community members like Vidas Matelis, Marco Russo, Stacia Misner, and Andrew Brust…  a bunch of SQL celebrities, like BrentO, SQLRockstar, and BuckWoody…  and a fascinating individual named Jimmy who Kasper and I hope to introduce to all of you soon.
  3. Presented a session on Best Practices for PowerPivot (with Dave Wickert) where they literally turned away as many people as they let in – we were all seated, room packed, people standing in the back, and doors closed 15 minutes before scheduled start…  so we just went ahead and started 15 minutes early.  Never had an experience like that, ever.  Even Denny Lee was locked out.
  4. Had several private meetings with members of the PowerPivot team, learned a bunch of things (some of which I can share, some I can’t, but everything bodes well for us)
  5. Talked with dozens of attendees, got their thoughts on PowerPivot, answered questions on PowerPivot, and staffed Microsoft’s PowerPivot booth on three different days.

So, from all of that, what did I learn?  Here ya go, as compressed as I can get it:

IT attitudes toward Excel are shifting dramatically

Excel Rebellion Circa 1977

“The more you tighten your grip against Excel, the more data will slip through your fingers.”

6-7 years ago when I attended BI conferences, Excel was widely regarded as the enemy, and there were even sessions titled things like “how to get your users out of Excel.”  The prevailing sentiment was that a responsible BI practitioner had a duty to replace Excel with dedicated BI tools like Cognos or Business Objects.

There has been nothing short of a seismic shift since then.  I’d like to think that has something to do with our efforts in Excel 2007 to legitimize Excel as a BI tool, but honestly, I think it’s just that the realization has sunk in…  no one is ever getting rid of Excel.  Furthermore I think it’s more than just capitulation – at this point most people realize that getting rid of Excel would be a bad idea even if it were possible.

Some favorite quotes from IT/BI pros:

“Fact is I get most of my best analytics ideas from my Excel users’ workbooks”
”We’ve been decriminalizing the use of ad hoc Excel”
”Data just wants to be free, data will find a way”
”Rob I am really impressed with your jumpshot, you could play near the highest levels in Holland were you in shape.”

(OK that last one was from Kasper – I felt obligated to include that since he kinda beat me in four out of five games and I need to save face.)

I very much think it’s time to revisit a few of my favorites posts on this topic, so if you have started reading recently, I encourage you to check out:

Microsoft Unveils New Programming Language XL#
Putting the “Intelligence” in “Business Intelligence,” Part 1
Putting the “Intelligence” in “Business Intelligence,” Pt 2
Putting the “Intelligence” in “Business Intelligence,” Pt 3

PowerPivot is arriving at precisely the right moment.  BI and IT pros are embracing it EXTREMELY eagerly.  Much more than we dared hope back when I worked on the engineering team in Redmond, and more than they realize even now I believe.  They are prepared to cooperate with their Excel users in order to bring about a more efficient data culture.  Which brings me to the next point…

PowerPivot does NOT need viral adoption!

novirus_sticker-p217678350895040539qjcl_400 Are you listening, PowerPivot team?  I’m going to keep standing up on every soapbox and hilltop I can find, screaming as loud as I can, that PowerPivot does NOT require grass roots adoption in order to be broadly adopted at a record pace!  That perceived need for bottom-up adoption lingers within the team from the early days, and it will be a shame, both for Microsoft and for the rest of us, if they continue to think that way.

Why?  For one, they will build the wrong feature set going forward if viral adoption is still a goal.  Originally for example, data cleaning features were intended to be included in v1.  The thinking here was that the Excel users could not remotely rely on IT to help them, or to provide them the right kinds of data, or even the right kinds of access.  That simply is NOT turning out to be true.

And if the team spends a ton of time building features that aren’t needed, it will subtract from the quality of the other features we get, and/or replace other features altogether.

Second, the marketing message is noisy today.  This dual “top-down through IT and bottom up through Excel user empowerment” message is not being well absorbed by the MS field.  By far, the Excel component is easier for the MS field to understand and repeat.  So it gets a lot more air time than the top-down message.  And as a result, an IT team that otherwise would have been enthusiastically receptive to the first message only get exposed to the “Excel gone wild” message and recoil from it.

This truly is a case of subtraction by addition and should be avoided :)

PowerPivot was the Buzz of the BI Conference

Even Wears the Right Color I expected PowerPivot to grab an outsized share of attention at the conference, but multiple people pointed out to me that it basically took over.  People from other companies stopped by at the PowerPivot booth to find out was going on, because everyone coming to their booth was asking things like “how does this integrate with PowerPivot?”

And the Microsoft messaging mirrored that.  PowerPivot dominated the BI keynote on Tuesday – we were all kinda surprised to basically only see PowerPivot demos for 90 minutes (seriously, the other MS BI teams had to be a little irritated by that.  You can watch the keynote here and see what I mean, and maybe see us smiling ear to ear in the first row).  It dominated the overall session count.  It showed up in sessions that weren’t about PowerPivot.

Kasper looked at me at one point and said “I really like the names of our websites.”  To which I replied, “I really like what we’ve been studying and practicing for the last nine months.”  OK for me it’s been several years.  Take THAT Kasper! :)

Kasper’s stuck on a plane for the next 24 hours and can’t fight back.  Muhaha.  OK, final observation for part one…

PowerPivot is evolving into a “Gateway Drug” in SQL11

That’s right, PowerPivot leads to stronger tools.  If you watched the keynote demos above, you saw something that’s pretty exciting once it sets in.  Remember that the Analysis Services product has existed for over ten years as a toolset that people like me weren’t able to use.  You could learn the query language MDX, but when I discovered that even a simple IF() statement required a PhD, I decided to do other things with my time.

PowerPivot, by contrast, is the kind of canvas on which I can paint.  Keep in mind, however, that PowerPivot is really TWO things:  1) It’s a toolset where someone like me can build models, applications, and reports   and 2) It’s the Vertipaq engine, the thing that makes monstrous compression and performance possible.

In theory, I guess, the PowerPivot toolset – the Excel addin, the DAX language, the integration with SharePoint – was possible without the Vertipaq engine.  Probably would have been too slow and unwieldy to gain much traction, but possible

The real question though was what Microsoft was going to do about the traditional Analysis Services product.  The existing AS product did NOT get equipped with Vertipaq in the 2008 R2 release, so in many ways the BI pros have been quite jealous of us :)  It was obvious that Vertipaq was going to find its way into AS proper in a future release, but it was not clear what the resulting product would look like.

OK, here ya go:  It’s gonna look like PowerPivot.  With an even larger data capacity.  And lots of features that we don’t currently have.  With no SharePoint dependency and no requirement that you embed the models in Excel files. 

And ok, to make it look industrial strength, they’re gonna put it in Visual Studio.  But that’s a minor detail to me, more cosmetic than anything.  I’m told it will support DAX, and generally use all the same sorts of Excel-user-friendly UI gestures that we are used to, as if the Excel addin were dropped into the Visual Studio frame.  No requirement that we change to MDX and other concepts in order to “upsize” into the more powerful future version.

Best news of the conference.

6 Comments | Observations, Trends, and Wild Speculation | Tagged: | Permalink
Posted by powerpivotpro

Calling a Stored procedure in PowerPivot

June 9, 2010

While building a new PowerPivot workbook i wanted to use Time Intelligent functions. One of the golden rules of PowerPivot time intelligent functions is to create a seperate related time table. I have created a stored procedure which creates a new table in PowerPivot. The important thing to do is when you want to call a stored procedure withing PowerPivot is set the “SET NOCOUNT OFF” at the beginning of your sproc. This will make sure your stored procedure will return only one dataset.

You can use this code to create the range:

/*
Get a range of dates in the range @fromdate until @todate
*/
create procedure getDateRange
@fromdate datetime, @todate datetime
as
begin
SET NOCOUNT OFF
/*Declare @todate datetime, @fromdate datetime
select @fromdate = '2005-01-01'
select @todate = '2008-12-31'*/

;With DateSequence( [Date] ) as
(
                Select @fromdate as [Date]
                               union all
                Select dateadd(day, 1, [Date])
                               from DateSequence
                               where Date < @todate
)

select
                               YEAR([date]) as Year,
                               Month([date]) as Month,
                               DAY([DATE]) as Day,
                               [date] as Date,
                               CASE Month([date])
                                                WHEN 1 THEN 'Jan'
                                               WHEN 2 THEN 'Feb'
                                               WHEN 3 THEN 'Mar'
                                               WHEN 4 THEN 'Apr'
                                               WHEN 5 THEN 'May'
                                               WHEN 6 THEN 'Jun'
                                               WHEN 7 THEN 'Jul'
                                               WHEN 8 THEN 'Aug'
                                               WHEN 9 THEN 'Sep'
                                               WHEN 10 THEN 'Okt'
                                               WHEN 11 THEN 'Nov'
                                               WHEN 12 THEN 'Dec'
                               END as [MonthShort],
                               DATENAME(MONTH,[Date]) as [MonthName],
                               max(DATEPART(DAYOFYEAR,[Date])) as DayInYear,
                               DATEPART( wk, [date])  as Weeknumber
from DateSequence
group by YEAR([date]),Month([date]),DATENAME(MONTH,[Date]), [date]
option (MaxRecursion 10000)

end
GO

Importing this

will give me:

Great tip got from Dave Wickert :)

7 Comments | Quick Tips & Tricks | Permalink
Posted by Kasper de Jonge

Get insight to SharePoint PowerPivot usage without Central admin rights using custom SSRS reports

June 7, 2010

Posted by: Kasper de Jonge

As a salute to the session: “Building Custom Extensions to the PowerPivot Management Dashboard” of Dave Wicker today at MS Bi conference a blog post on customizing  the dashboard.

I just love the PowerPivot management dashboard, I think it’s a great dashboard and shows you all kind of information you need to maintain your PowerPivot environment. Unfortunately the management dashboard is placed inside the Central admin.You need access to the central admin  to be able to visit the PowerPivot management page, this means you also have access to all the other SharePoint admin pages.

I can understand your sharepoint admins aren’t too happy when your BI department is running around in your central admin.

To solve this have I have created a report based on the data inside the PowerPivot Management Dashboard data, that you can use without having the credentials. For more information check Dave Wickerts blog post: Customizing the PowerPivot Management Dashboard (too bad i had finished my report when this article was released )

The report will show you the usage of reports by users, nr of queries and load in the last 7 days and in the last 30 days. This way you can quickly identify which workbooks is being used a lot and you need to take a look at as BI department.

The data is placed inside a PowerPivot file which is stored at Central Administration site (Go to Site Actions -> View All Site Content -> under Document Libraries click PowerPivot Management folder). The file will be placed inside the directory with the name of a guid which you see there. Inside this folder you find “PowerPivot Management Data.xlsx” which is the PowerPivot file which contains the PowerPivot usage data.

We are going to create a user in the AD and give this user access to the central admin and use this to run our report with:

One thing to keep in mind, you need to use a reporting services that is integrated in the same SharePoint, i was unable to connect to the data using a separate reporting services. I guess this has something to do with claims / kerberos.

Ok on to the report. I created a report in BIDS. First I created a shared datasource connection using a Microsoft SQL Server Analysis Services connection type pointing to: “data source=http://sp2010rc:5566/PowerPivot Management/466f3002-543f-4c06-93bf-4d922641a73f/PowerPivot Management Data.xlsx”.  So we can reuse this connection for multiple reports we can build in the future. Change this link to the location of your PowerPivot Management Data.xlsx file.

Next i created the report, in this report i wanted to show the usage from the last 7 and 30 days of my PowerPivot Files. To get the data I altered a SSRS generated dataset in MDX to always return the last 7 days.

To determine the measure I wanted i used the “Workbook Activity.xlsx” provided in the management dashboard. The three measure used there to determine workbook activity are:

  • UserDCnt : Number of distinct users making connections to workbooks
  • QuerySum : Total querys fired in requests at workbooks
  • SizeMBMax : Total size of the load put against the server at each request, as i understand this is the size of the powerpivot data.

    • For dimension members i selected:

    • [Date].[Month] : Month a request was done in
    • [Date].[Year] : Year a request was done in
    • [Date].[Date] : The date a request was done in
    • [Documents].[AuthorLoginName] : Author of the document requested
    • [Documents].[FileUrlRelativeToSite] : Filename of the document requested
    • [Users].[FullName] : user that requested the filename

    This results in the following MDX statement:

    SELECT NON EMPTY { [Measures].[UserDCnt], [Measures].[QuerySum], [Measures].[SizeMBMax] } ON COLUMNS,
	  NON EMPTY {
				(	[Date].[Month].[Month].ALLMEMBERS *
					[Date].[Year].[Year].ALLMEMBERS *
					{ ParallelPeriod([Date].[Date].[Date], 7, StrToMember("[Date].[Date].[" + Format(now(), "yyyy-MM-dd") + "]"))
						: StrToMember("[Date].[Date].[" + Format(now(), "yyyy-MM-dd") + "]") }*
					[Documents].[AuthorLoginName].[AuthorLoginName].ALLMEMBERS *
					[Documents].[FileUrlRelativeToSite].[FileUrlRelativeToSite].ALLMEMBERS *
					[Users].[FullName].[FullName].ALLMEMBERS
				)
				}
	DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Sandbox]

    Take notice on the Date part:

    { ParallelPeriod([Date].[Date].[Date], 7, StrToMember(“[Date].[Date].[" + Format(now(), "yyyy-MM-dd") + "]“))
    : StrToMember(“[Date].[Date].[" + Format(now(), "yyyy-MM-dd") + "]“) }
    What happens here is that i only want to select the measures that in  the date range from 7 days ago to today, we can do this because the date dimension contains all dates and thus can do a strtomember. You can change this number to whatever you want, or maybe even make it a parameter in ssrs.

    Putting this in a report i show the powerpivot file together with its author, total nr of users, nr of queries and the size of the workbook, then i used a sparkline to show the nr of users for each day, and in a databar i show the numer of querys * the size of the workbook to show the load it has on the server.

    This results in the following report:

    Too bad that I didn’t have more data in my management dashboard so the trend doesn’t really come out as it should, but it should give you an idea what you can do. When we actually will have more data we can fine tune this report.

    I have published this report to SharePoint and made sure the report is executed as the user we just created and gave access to the central admin:

    The report will now have access to the powerpivot management data while the report user doesn’t.

    Feel free to download the the SSRS 2008 R2 project here. Tell me what you like or dislike from it or what you changed.

    Leave a Comment » | Uncategorized | Permalink
    Posted by Kasper de Jonge

    PowerPivot time intelligent functions: why use ALL() and how to work around it

    June 3, 2010

    Posted by: Kasper de Jonge

    Last week I got a most excellent question from Sasha at my question page. Using my timeintelligent function screencast Sasha created a workbook using  YTD  where he used one table with facts and dates in it. The problem he had was when created his time intelligent function he wasn’t able to use data from other columns than the date columns. In this blog post i´ll try to describe how the time intelligent functions work, what pitfalls are and how to solve them.

    To be able to use a time intelligent function in DAX you use the Calculate function to group measures by a filter. With a time intelligent function you want to filter your values over a period of time (like YTD or previous month).  Most of the time you want the use these functions inside a pivottable where you use dates on the x or y-axis, the values inside the pivottable would show values per the current period context. This would logically result in the following DAX formula:

    Dates YTD = CALCULATE(sum(Tablix1[nroforders]),DATESYTD(Tablix1[Date]))

    This writes out to: you want the sum of Tablix1[nroforders] from the first Tablix1[Date] value of the year to the Tablix1[Date] belonging to the Tablix1[nroforders] in pivottable context. The YTD of the Tablix1[nroforders] from march 2009 would mean we need to take the sum of Tablix1[nroforders] from all rows from the start of year to march 2009, in the image below you can see a sample of the values that will be summed:

    But when we add the formula to the measures and use it in a pivottable we see something strange:

    As you can see the nroforders and the YTD formula result in the same values … this is not what we expected. The reason is because the time intelligent function requires an additional parameter, in a blog post at PowerPivotpro.com the PowerPivot product team gave the following answer to my question to why the result is not as expected:

    When using Time intelligence functions like LASTDATE or DATESMTD, it is necessary to add another argument which is ALL (TimeTable). Without this only the selected dates are considered, and you can’t find the last month unless you are in the last month.

    As you can see in the screenshot this is indeed what happens, the function only uses only the current date context in the sum.

    Ok so we need to use ALL to get the results we want, this will result in the following syntax:

    DatesYTD w All =CALCULATE(sum(Tablix1[nroforders]),DATESYTD(Tablix1[Date]),all(Tablix1))

    This is indeed the result we expected, but having to use the ALL() function has a huge downside.  The ALL() function according to BOL:

    Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.

    This means when you use a filter or slicer this is being ignored by the time intelligent function that uses ALL(). As you can see below the results of the YTD are the same as unsliced:

    There are two methods we can use to work around this problem:

    1. When you know what slicers and filters you want to work with you can use an ALL() alternative: ALLEXCEPT(). With ALLEXCEPT you can pass through “a list of columns for which context filters must be preserved”.
      In our case we would like to be able to slice on country. This would look like:
      DatesYTD w AllExcept =CALCULATE(sum(Tablix1[nroforders]),DATESYTD(Tablix1[Date]),allexcept(Tablix1,Tablix1[country]))
      I would say use this option if you already know what you want to slice on and don’t have much time to solve it properly.
    2. The proper and most flexible is option number 2. To be able to slice / filter on all the columns you can think of you should create a separate time table. This isn’t very user friendly and your end users will have a hard time grasping this. A few options i can think of to create this time table:
      1. Import the fact table with distinct on date columns (i hope your table isn’t too big ..)
      2. use excel to copy the date rows, remove duplicates and create a linked table (new dates won’t be added)
      3. Supply your users with a default time table in SharePoint/SSRS, they can import this by using the data feed option, use your DWH datetime table as source.

    you should create a relationship on the datetime column between the fact table and the the imported time table (make sure your datetime columns have identical granularity, like year, month, day, otherwise the join wont find results). With this relationship in place you now are able to use ALL over the datetime table. When you use ALL() over the separate time table it no longer ignores filters / slicers over your  fact table.
    The function would look like:
    DatesYTD w All TT =CALCULATE(sum(Tablix1[nroforders]),DATESYTD(DateTable[Date]),all(DateTable))
    You now are able to filter or slice all the columns from the fact table you want. In the RTM version of PowerPivot the ALL() in the DAX function is no longer required, the separate time table unfortunately still is.

    The final workbook now looks like:

    My conclusion is that Time intelligent functions still are a very powerful feature but i am really disappointed on its user-friendliness, while these workarounds are easy for IT/BI personnel to grasp and implement, end users will have a harder time implementing this. Maybe MS can implement a “add date time table” button in PowerPivot to automatically create a date time table to our PowerPivot tables to make it a little easier for end users to implement time intelligent functions.

    Leave a Comment » | DAX, Non-Football Examples | Permalink
    Posted by Kasper de Jonge

    Review new PowerPivot workbooks on SharePoint using approval workflows

    June 1, 2010

    Posted by Kasper de Jonge

    Now something not really about the PowerPivot engine but something i think will be very nice to set up on yourSharePoint environment.

    At the excellent linkedin discussion on PowerPivot, someone suggested that we might make review of a PowerPivot file possible. I answered we can because the PowerPivot gallery is a document library and we can manage approval workflows on this gallery. But i also wanted to check it out how I can do this.

    In this blog post i’ll show you how to configure a PowerPivot gallery so that all new PowerPivot workbooks published to a PowerPivot gallery are reviewed by IT. I used a blog post i found which explains howto configure approval workflows step by step.

    We start at our PowerPivot Gallery:

    To enable workflows we go to Workflow settings in the library tools:

    Select the current library and click add new workflow, we now get to our workflow settings:

    We want to use the Approcal – SharePoint 2010 workflow, set a appropriate name  ”PowerPivot approval”, we want a new task to be created at the reviewers task pane for every document that is added to the library. Next, we define the history list for the workflow. History lists are special logs which monitor the execution of the workflow. At each step of the workflow, messages at written to the log upon execution of the step. I want the workflow only to be executed when i upload a new file to the gallery:

    We now can set up who the approvers are responsible of reviewing the PowerPivot workbooks, the settings are pretty straightforward.

    Assign approvers, enter a request they will see at the workflow. The other settings aren’t necessary to complete the workflow.

    We now can save our workflow and it will be in effect immediately.

    When i now upload a new document to my PowerPivot gallery, we can see that the PowerPivot approval is in progress:

    When i would look at my tasks lists within the same site collection i would see my new PowerPivot sheet is awaiting my review:

    And we can open this workflow and comment on it, we can also open the document to look inside it:

    We just approve my workbook now. When we check back at the gallery to see our workbook is approved:

    So using workflow we can make an additional step in our Managed self service BI, and it is very easy to set up.

    Leave a Comment » | Non-Football Examples, Quick Tips & Tricks | Permalink
    Posted by Kasper de Jonge

    Building a cash flow statement in PowerPivot using dynamic measures in DAX

    June 1, 2010

    Posted by Kasper de Jonge

    I got an excelent question at my “Ask a question page” on my blog. Greg asked me if i knew of a sample for a cash flow statement in PowerPivot. I did not know of one, and to be honest i didn’t even know what a cash flow statement was :).

    Searching for a sample i found this picture of a cash flow statement:

    This made things more clear. We want to see income and expense in two different tables. And then in a new table the cash flow statement where we can see the starting cash at the start of a month, income and expense in the month and the ending cash with the values at the end of the month.

    I got really excited to solve this using PowerPivot, so I decided to build a sample myself. First i had to create a fact table that contains the values of the income and expense, i used the picture from above to create sample data:

    As you can see we have income and expense for days in three months. To determine if a specific activity is income or expense, i created a secondary table that groups my activities:

    We can create a relationship between the two tables so we can put them in a table and calculate a measure with income – expense.

    Next i created a separate related time table to use in time intel. functions:

    We create relationships between the date from the facttable and the new date table

    Now we can create the first table putting income and expense in one table per month using only the relationships between the tables and no DAX:

    Now for the interesting part, how do we create the cash flow table.

    What we really need is a way to create 4 different calculations per row for each month on column, as we can see in our sample we need to have the following measures:

    1. Starting cash= the total ytd of (sum of disbursements – sum of receipts) until the previous month
    2. Receipts = sum of Receipts at the current month
    3. Disbursements = sum of Disbursements at the current month
    4. Ending cash = the total ytd of (sum of disbursements – sum of receipts) until the current month

    These measure will translate to dax as the following:

    1. TOTALYTD(Activities[Sum of Value](‘Group’[group] = “Receipts”) – Activities[Sum of Value](‘Group’[group] = “Disbursements”),DATEADD(‘Date’[Date],-1,MONTH))
      subtract values from group disbursements from values of the group receipts for the totalytd until the previous month
    2. Activities[Sum of Value](‘Group’[group] = “Receipts”)
      values from group  receipts in the current month (current column context)
    3. Activities[Sum of Value](‘Group’[group] = “Disbursements”)
      values from group disbursements in the current month (current column context)
    4. TOTALYTD(Activities[Sum of Value](‘Group’[group] = “Receipts”) – Activities[Sum of Value](‘Group’[group] = “Disbursements”),’Date’[Date])
      subtract values from group disbursements from group  receipts for the totalytd until the end of the current month

    But how can we do this in PowerPivot ? We need to determine what to calculate per row .. to do this we can use dynamic measures in DAX, as in this excellent post from MSFT Howie Dickerman.

    First we need to be able to put values on the rows on which we can base our measures, to do this i created a new table in excel with rows and loaded it into PowerPivot:

    We now can put these row labels against months in a new pivottable:

    Now we can create a measure which uses the current row context to determine what to calculate at the measure. When we use the VALUES function we can see what the current row context for our Cashflow[CashFlow]  is and use this in an IF  statement in the measure to calculate a different measure for each row.

    The formula will look like:

    = IF(COUNTROWS(VALUES( Cashflow[CashFlow])) =1,
    IF( VALUES(Cashflow[CashFlow]) = "1 Starting Cash",  TOTALYTD(Activities[Sum of Value]('Group'[group] = "Receipts") - Activities[Sum of Value]('Group'[group] = "Disbursements"),DATEADD('Date'[Date],-1,MONTH)) ,
    IF( VALUES(Cashflow[CashFlow]) = "2 Receipts",  Activities[Sum of Value]('Group'[group] = "Receipts"),
    IF( VALUES(Cashflow[CashFlow]) = "3 Disbursements",  Activities[Sum of Value]('Group'[group] = "Disbursements"),
    IF( VALUES(Cashflow[CashFlow]) = "4 Ending Cash", TOTALYTD(Activities[Sum of Value]('Group'[group] = "Receipts") - Activities[Sum of Value]('Group'[group] = "Disbursements"),'Date'[Date]) ,
    BLANK())))), Activities[Sum of Value])

    This will create one measure that depending on the row context shows different calculations.
    Resulting the actual cashflow pivottable:
    You can see that the values are calculated per cashflow type per month showing a different calculation. I hope this was what Greg meant :)
    This example shows again the great power of DAX, a lot of things are possible. I still get really excited about PowerPivot.
    The sample file can be downloaded from my skydrive.

    3 Comments | DAX, Non-Football Examples | Tagged: | Permalink
    Posted by Kasper de Jonge

    The PowerPivot job posting I mentioned earlier

    June 1, 2010

    Posted by Rob

    Here it is:

    image

    That was posted nearly two months before PowerPivot was released :)

    It was available last week but as of today the listing appears to have been taken down, filled.

    Which one of you fine folks grabbed it? :)

    2 Comments | Observations, Trends, and Wild Speculation | Tagged: | Permalink
    Posted by powerpivotpro

    Kasper de Jonge joins PowerPivotPro!

    June 1, 2010

     
    Custom Wooden Hoops Clogs

    “Hey!  Where’d you get that pic??”

    -Kasper “The Killer Ghost” de Jonge

    Guest Poster Becomes Co-Owner!

    Over the past few months, Kasper has provided a number of excellent guest posts – the most prolific guest poster in this blog’s short history in fact.

    I have to wait until next week to find out exactly how good Kasper is at hoops.  But I already know he plays a mean game of PowerPivot.  In fact he plays a mean game of… numbers in general.

    Quite simply, he receives the PowerPivotPro “Zero Doubt” stamp of approval.

    OK…  what does this mean?

    Pretty simple really.  You get more good advice, examples, and tips.  Kasper has a ton of great stuff to share, like the post above, and I thought it was time everyone saw more of it.  We’re taking off the “guest post” flag for Kasper and giving him a set of keys to the car.

    In all honesty, I learn at least as much from Kasper as he does from me.  We have different backgrounds – he has more experience in traditional BI than I do, and I have more Excel experience.  He and I chat all the time, swapping observations and techniques – via email, IM, Skype, and Twitter.

    Rob, are you still going to post?

    Yes, I  have no plans to stop blogging.  In fact you’ll be seeing even more of me going forward once I’m done launching our company’s PowerPivot-based product line.

    My evil long-term plans exposed!

    Fact is, I want the name “PowerPivotPro” to not just be an alias for “Rob Collie.”  I want it to mean something more than that.  A community.  A designation – where people say “I am a PowerPivotPro.”  It’s a new style of professional that’s just getting off the ground, and in the future we will see PowerPivot skills and certification specifically targeted by recruiters, internally trained for within corporations, and listed on resumes in the same way someone would list C++ or T-SQL as their primary skill today.

    Go back and read this post for a clearer idea of what I’m talking about.  I absolutely believe we are headed for a world like that.

    Kasper and I just happen to be early to the party.  I suspect there aren’t many people in the world so far who have been paid specifically to provide PowerPivot expertise (which we both have).

    But it’s ramping up faster than you might think.  I just saw a job posting where the recruiter mentions PowerPivot specifically as a “must-have” or “must-learn."  Cool huh?  It hasn’t even been released for a month :)

    So, welcome Kasper.  The Bio Page has been updated to reflect his presence on the staff and certification as a trusted PowerPivotPro.

    More may join us over time.  Rest assured that the bar will remain high :)

    And Kasper, I’m still gonna do my absolute best to crush you next week :)

    4 Comments | Hello World / Administrivia | Tagged: | Permalink
    Posted by powerpivotpro

    « Previous Entries
    Next Entries »