0

Please could you assist. I am using the following expression to obtain the inflow for the same week for the previous month.

([Time].[Week Commencing Date].CurrentMember.PrevMember, [Measures].[_Inflow])

This appears to be returning results, however the grand totals are missing as this would be summing the weeks and assigning the value into a Month Year Total.

If i select Month Year in my time dimension, i get no results.

Could you please show me an example of how i can correct the expression.

I have tried [Time].[Time].CurrentMember.PrevMember, [Measures].[_Inflow] so that it uses the Time hierarchy but this does not work.

Time Dimension - Attribute: Month Year, Quarter, Week Commencing Date - Time(Hierarchy): Year, Quarter, Month Year, Week Commencing Date

Any help would be appreciated. Thank you.

Justin
  • 9,634
  • 6
  • 35
  • 47
M.Developer
  • 35
  • 4

1 Answers1

0

You need to use Cousin function .Take a look at the example below, I dont have week in the sample cube at hand in the hierarchy therefore I am using Date. In this example I am trying to get the same date for the previous month.

select
{[Date].[Calendar].[Date].&[20130922],
cousin([Date].[Calendar].[Date].&[20130922],[Date].[Calendar].[Month].&[2013]&[8])
}
on columns,
[Measures].[Internet Sales Amount]
on rows from 
[adventure works]

The Date UserHierarchy is Year- Semester- Quater- Month- Date

enter image description here

EDIT: Use Cousin as calculated member

To use the cousin function as calculated member you need to do the following. But make sure, that you have the date part used in the query.

select
{[Date].[Calendar].[Date].&[20130922],
cousin([Date].[Calendar].[Date].&[20130922],[Date].[Calendar].[Month].&[2013]&[8])
}
on columns,
[Measures].[Internet Sales Amount]
on rows from 
[adventure works]

enter image description here

MoazRub
  • 2,881
  • 2
  • 10
  • 20
  • Thank you. I will give this a go. Much appreciated. – M.Developer Jan 31 '19 at 08:46
  • @M.Developer you are welcome. Kindly do share your experience. – MoazRub Jan 31 '19 at 16:38
  • @M.Developer did it solve the issue you were facing? – MoazRub Feb 04 '19 at 11:43
  • I get #Value error. I believe this is due to the calculated measure that i'm creating. Can i use this without specifying a date? How would i write this ? Another way to solve it would be to bring through the previous values within the database physical table which requires more work. i tried amending this ([Time].[Week Commencing Date].CurrentMember.PrevMember, [Measures].[_Inflow]) to use the cousin function without specifying a date – M.Developer Feb 05 '19 at 12:44
  • @M.Developer check the edit, I shows how to use cousin as a calculated measure – MoazRub Feb 05 '19 at 15:49
  • @M.Developer any luck? – MoazRub Feb 07 '19 at 12:54
  • Thank you. Yes, works as expected. I will need to add to this at a later point but works. Thanks for your help. – M.Developer Feb 08 '19 at 14:12
  • @M.Developer You are welcome. It is always nice to mark solutions that work as answers. This helps anyone else having the same issue. – MoazRub Feb 08 '19 at 14:26