I have created a measure using Aggregation based on Dimension when i am using this measure to create

Question

I have create 1 measure using complex aggregation(Aggregation based on Dimension) i am creating Headcount measure using Sum and Last Aggregation

After that i am creating another Measure Year Ago Measure based on this Headcount Measure but this Year Ago measure which i have created is not giving desired result.

Did anyone faced this problem before and Please provide me the solution and Thank in advance.

Thomas Dodds · Answer

Your issue is solved with a physical structure not a logical one.

3057791 · Answer

Hi Thomas,

Thanks you this solution on the Periodic Snapshot Table, but is there any other way to do it from RPD.

Thomas Dodds · Answer

Use of information drives physical design ... so in your case you want to know a count at a particular point in time AND what it was at a referential point in time past.

To do this you need to take snapshots of the counts as they are - so then you can look back.  It is far to involved to be trying to look back in time to figure out what the count was at any point by starting with history and adding all the hires and removing the fires/transfers on every request.

Here's a good write up from Kimball on the types of fact tables:

http://www.kimballgroup.com/2008/11/fact-tables/

What you would need is a Periodic Snapshot Grain table.

To make this 'thinkable' -- I assume you look at your bank balance occasionally ... but to know what it was a few months ago you look at an old statement.  That statement is a periodic snapshot of your account at that time and you probably get one monthly.   You could take your own weekly snapshot of your account by printing the web page (or getting the ATM receipt) weekly - nonetheless those are snapshots.