Point in time analysis perfected: Part 2

It is funny how some ideas are not thought of but just fall in to your lap :). My answer to a question posted on my last blogpost resulted in a more efficient solution to “point in time analysis” then the one I posted about. I think this particular improvement on a exisiting solution is a nice example on how most solutions are not only about applying technique but mostly about applying (business)logic.

How the old version looks like:

=CALCULATE(SUM(Assignment[Planned hours]);

FILTER(Assignment;

Assignment[Valid from date] <= MAXX(VALUES(Period); Period[Date])

&&

Assignment[Expiry date] >= MAXX(VALUES(Period); Period[Date])

)

)

How it works: Give me the total “Planned Hours” of all assignment rows in table “Assignment” of which the “Valid from date” is smaller or equal to the selected “Period Date” and the “Expiry Date” greater then or equal to the selected “Period Date”. This solution requires a unrelated date dimension table (in this example it is called Period). This approach needs a unrelated date dimension table.

How the new version looks:

=CALCULATE(SUM(Assignment[Planned hours]); FILTER(Assignment; Assignment[Valid from date] = MAXX(VALUES(Assignment[Valid from date]); Assignment[Valid from date])))

How it works: Give me the total “Planned Hours” of all assignment rows in table Assignment of which the “Valid from date” is equal to the max “Valid from date”.

The main difference? No second MAXX iterator! Which is one less table to iterate. On large tables this can make a huge difference. Another perk you get from this solution is that you do not need to have a unrelated date dimension.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s