Skip to content

Instantly share code, notes, and snippets.

@pstoellberger
Last active January 4, 2016 06:29
Show Gist options
  • Save pstoellberger/8581825 to your computer and use it in GitHub Desktop.
Save pstoellberger/8581825 to your computer and use it in GitHub Desktop.
WITH
member Time.CurrentDa as ([Time.Weekly].CurrentMember.Level.Name = "(All)")
member Measures.Now as
IIF ([Time.Weekly].CurrentMember.Level.Name = "(All)",
IIF ([Time].CurrentMember.Level.Name = "(All)",
0
, [Time].CurrentMember)
,[Time.Weekly].CurrentMember
)
member Measures.Prev as
IIF ([Time.Weekly].CurrentMember.Level.Name = "(All)",
IIF ([Time].CurrentMember.Level.Name = "(All)",
0
, [Time].CurrentMember.PrevMember)
,[Time.Weekly].CurrentMember.PrevMember
)
member Measures.Gain as
CASE WHEN Measures.Prev > 0 THEN ( Measures.Now - Measures.Prev) / Measures.Prev
ELSE null
END, FORMAT_STRING = '#0%'
SELECT
NON EMPTY {[Measures].[Unit Sales], Measures.Gain} ON COLUMNS,
NON EMPTY {[Time.Weekly].[Week].Members} ON ROWS
FROM [Sales]
--Screenshot: http://cl.ly/image/1M2k2Y1E0c1X
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment