PowerPivot: Using EARLIER() to add a group of rows in related table

I have been using PowerPivot for quite some time now, and I it has helped me create some nice dynamic reports without the need to commit hours of specialized BI development.

Even though working with this tool is pretty much intuitive, there are some DAX expressions that require extra investigation. (DAX is Data Analysis Expressions). The EARLIER() function was such an expression..

The need presented itself while I was working on my latest report and it seemed something pretty straight forward. Nevertheless I had to try out several things without luck, until I found EARLIER().

Here's what I wanted to do:

I had 2 tables: HHRR Costs, with all the hours put in each project, down to the month, and table Project Income, which had each project income down to the month as well.

The relationship was created over a calculated column on both tables by concatenating year, month, and project name. This is a common procedure to define 1-to-1 or 1-to-many relationships on tables that don’t have unique values in a single column. So I created a calculated column [Year_Month_Project] = [Year]&[Month]&[Project] on each table and connected them.

Here comes the tricky part...

Now I needed to perform some calculations in the HHRR Costs table, and for that I needed to know how much was the total income of each project, referenced in each row of the HHRR Costs table :). For example, something like this:

($10,000 was the total income of the project over its 3 month duration.)

First thing I tried was:

[Total Project Income] = RELATED('Project Income'[Project Income]).

But this gave me the project income for each month, because thats how the relationship was set (at the month level), so I would get something like this:

Then I went on trying several combinations with the SUM, FILTER and SUMMARIZE functions without any luck. I needed to tell PowerPivot that for each of the rows in this table (HHRR Costs), go to the Project Income table, and add up all the values in the Income column but only for the related project (current row in HHRR Costs).

So I tried something like this:

[Total Project Income]=SUMX(FILTER('Project Income','project income'[project]=[Project]), 'Project Income'[Income])

Close, but not quite it, because the context of [Project] was not the current project... So instead of getting the total project income, I got the sum of all the projects in every row.

The solution came to me when I discovered the function EARLIER(), and here is how you should do it:

[Total Project Income]=SUMX(FILTER('Project Income','project income'[project]=EARLIER([Project])), 'Project Income'[Income])

For a clear explanation of EARLIER click here. Basically EARLIER will lock the current Project value through the SUMX iteration. Thus it will perform exactly what I needed:

For each row in HHRR Costs>

1. Filter Project Income table by the current project name
2. SUM [Income] column of the group of previously filtered rows

Also, here is a good one about SUMX

Thanks

UruIT http://www.uruit.com/offshore-custom-software-development-uruguay.html