I have yearly sales goals in a measure called "Target" and a date dimension called DimCalendar
.
Looking at the underlying data, I'm thinking I should get a value different than what the below query returns. It's my understanding that this query will get the value for the "Target" measure where the associated year is 2016 (one year in the future or -1) and for a specific account.
SELECT
{[Measures].[Target]} on columns,
{ParallelPeriod(
[DimCalendar].[Year].[Year]
,-1
,[DimCalendar].[Year].&[2015])} on rows
FROM [MySalesCube]
WHERE { [Account].[Account].&[2025] }
This query returns
1944768
However, the underlying data seems to add up to only 162064
Nope, looks like there is an issue with the data after all after using the cube browser. Got to go revisit my ETL process.
This is what you have specified:
ParallelPeriod(
[DimCalendar].[Year].[Year]
,-1
,[DimCalendar].[Year].&[2015])
It means the following:
Take the year 2015
Then jump the specified number of periods, in your case -1, using the level specified, in your case [Year]
The following should (I think) be a simplified but equivalent version - if the first argument is missed out then it just uses the level of the third argument:
ParallelPeriod(
-1
,[DimCalendar].[Year].&[2015])
Although I think you can just use lag
to make everything more readable:
[DimCalendar].[Year].&[2015].LAG(-1)
Then again there is no point using 1 or -1 inside lag, as we have the functions NEXTMEMBER
and prevMEMBER
this could just be simplified to the following:
[DimCalendar].[Year].&[2015].NEXTMEMBER