Dear Friends,
In this post I will describe on of the last problems that I was involved.
The Problem
I have millions of rows in a FactTable with fields ID, Date and Amount… (I have more fields in this table, but for this post is irrelevant).
In AS I will have these rows aggregated by Time, so, for each day I have an amount. For each row, I need to create a calculated member for each day with the value amount of a reference date. The formula is:
Reference Date = Last Date of previous year of [My Field Date] of each row in FactTable.
The Solution
I posted my problem in MSDN forums, and I got an answer from Bryan C. Smith but didn’t resolve my problem.
He gave me an example of Adventure Works, but did not work in my project.
In this post I will describe on of the last problems that I was involved.
The Problem
I have millions of rows in a FactTable with fields ID, Date and Amount… (I have more fields in this table, but for this post is irrelevant).
In AS I will have these rows aggregated by Time, so, for each day I have an amount. For each row, I need to create a calculated member for each day with the value amount of a reference date. The formula is:
Reference Date = Last Date of previous year of [My Field Date] of each row in FactTable.
The Solution
I posted my problem in MSDN forums, and I got an answer from Bryan C. Smith but didn’t resolve my problem.
He gave me an example of Adventure Works, but did not work in my project.
So, I was looking for several MDX functions, and after a lot of failed attempts I founded the solution!
In this statement, I'm using the ClosingPeriod and Ancestor MDX functions. The ancestor to get the last year, and the ClosingPeriod to get the last value of that year in the hierarchy day.
I hope this post help you!
Regards!
No comments:
Post a Comment