Average of Last 90 days data, but for each hour samples in a day using MDX query

0
votes

I have a measure as 'power' rollup as SUM, Time dimension as Year->Month->Day->Hour, and Location Dimension.

We are storing at hour level of power data.

Now we require for a particular location and date, last 90 days average power for each hour. It should display 24 columns with average last 90 days power_total for each hour.

The following query gives only that date's power for each hour for a location1. But I need last 90days average power for each hour in a date and location. How to do this requirement?

SELECT {Hierarchize({[Time.Monthly].[2013].[9].[20].Children})} ON COLUMNS, NON EMPTY {CROSSJOIN({Hierarchize({[Location].[Location1]})},{[Measures].[power]})} ON ROWS
FROM [Power_Trend]

I am pretty new to MDX.

Please help me out.

-Prakash

2
mdx

2 Answers

0
votes

You can use LAG function to create a date range: description with some simple examples

Secondly you should create current date member with strtomember, for example:

strtomember("[Time.Monthly].["+cstr(year(now())) + "].[" + cstr(month(now())) + "].[" + cstr(day(now())) +"]")