Saturday, December 7, 2013

How to make relative period without using relative function

Please see sample below,

If the relative functions are not enabled, it is very difficult to write any report with relative time. To enable this functionality, you need specify make time dimension “Always (OLAP Compatible)”
However, if you cannot enable this function, Is there any workaround to resolve relative periods without using relative function. The answer is YES, using rank and subset.

Rank: rank ( [Sales (analysis)].[Time].[Time].[Year].[Year] asc)
Prior 2 Years:  subset ([Year],rank ( [Sales (analysis)].[Time].[Time].[Year].[Year] asc )-3,2)
Total of prior 2 years:  total ( [Sales (analysis)].[Sales].[Revenue] within set [prior 2 Years])

No comments:

Post a Comment