# Thread: Average of Last 90 days data, but for each hour samples in a day

1. Junior Member
Join Date
Sep 2013
Posts
6

## Average of Last 90 days data, but for each hour samples in a day

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]

Location MeasuresLevel 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
Location1 Power 60.00 60.00 60.00 60.00 60.00 60.00 60.00 60.00 60.00 60.00 70.17 77.17 76.50 76.17 71.33 72.67 72.00 72.83 72.00 72.00 60.33 50.00 50.00 50.00
I am pretty new to MDX.
-Prakash

2. Senior Member
Join Date
Jan 2013
Posts
796
Just to make sure I understand, you're looking to get the average over the past 90 days by hour, right? I.e. the average power consumption at 8:00 over the the last 90 days. Is that it?

That could be tricky to express in MDX. The ParallelPeriod() function works well to bring back the corresponding time member from a different part of the hierarchy, but that's just *one* member--it doesn't return a set. It seems like you really need a (plural) ParallelPeriods() function that can return the set going back 90 days, then average that set up, but unfortunately that doesn't exist.

If you can make changes to your schema, one option could be to add a new "Hours" dimension, along with an calculated measure that averages the preceding 90 days. You could then get the average for the 90 days broken down by individual hour. E.g.

with member measures.[avgPowerPast90Days] 'avg(tail(periodstodate([Time.Monthly].[Year], [Time.Montly].[2013].[9].[20]),90), measures.[power])'
select non empty [Hour].[Hour].members on 0, measures.[avgPowerPast90Days] on 1 from [Power_Trend]

HTH

3. Junior Member
Join Date
Sep 2013
Posts
6
Just to make sure I understand, you're looking to get the average over the past 90 days by hour, right? I.e. the average power consumption at 8:00 over the the last 90 days. Is that it? ---> That is correct.

I have a option to change schema. But 90 days is a variable. It can change to any number any time. So I can not calculate average before hand. That is where we have trouble.

4. Senior Member
Join Date
Jan 2013
Posts
796
I think you could parameterize an average calculation like the one below to allow a variable number of days

avg(tail(periodstodate([Time.Monthly].[Year], [Time.Montly].[2013].[9].[20]),90), measures.[power])

The trick would still be to breakout [Hour] as a separate dimension, to allow placing it on opposite axis, or crossjoining.

5. Junior Member
Join Date
Sep 2013
Posts
6
It worked. Thanks very much. I have created new hierarchy with only [Hour] level and executed. It worked. Thanks very much.

WITH MEMBER [Measures].[avgPowerPast90Days] AS 'avg(tail(PeriodsToDate([Time.Monthly].[Year], [Time.Monthly].[2013].[9].[20]),90),[Measures].[Power])'
SELECT NON EMPTY [Time.Hourly].[Hour].members ON COLUMNS , [Measures].[avgPowerPast90Days] ON ROWS from [Power_Trend]

Thanks very much.

6. Senior Member
Join Date
Jan 2013
Posts
796
Awesome. Glad to help.

7. Junior Member
Join Date
Sep 2013
Posts
6
I did some research on PeriodsToDate function. It has some flaws; as the 1st parameter is year, it will find set in the same year. So before March 31, it wont give correct result for 90 days. So after taking your hint on separate Hours dimension, I am able to find the average using range query as below. It works fine and gave the correct result.
But I have an issue for range query.
If there is no data for lagged days, then no result, even if data exists for 88 lagged days. This is very generic issue for range queries. Is there any solution?

WITH MEMBER [Measures].[avgPowerPast90Days] AS 'avg({[Time.Monthly].[2013].[10].[3].Lag(89):[Time.Monthly].[2013].[10].[3]})',FORMAT_STRING = "###.00"
SELECT NON EMPTY [Time.Hourly].[Hour].members ON COLUMNS ,
NON EMPTY {CROSSJOIN({Hierarchize({[BuildingHierarchy].[11111]})},{[Measures].[avgPowerPast90Days]})}
ON ROWS from [Power_Trend]

8. Senior Member
Join Date
Jan 2013
Posts
796
Are you talking about the case where there is no dimension member on one side of the range? I.e. if the first month in your time dimension is [Feb 2007], and you do a [Mar 2007].Lag(2)?

9. Junior Member
Join Date
Sep 2013
Posts
6
Are you talking about the case where there is no dimension member on one side of the range? - Yes. I am talking about that

10. Senior Member
Join Date
Jan 2013
Posts
796
You can wrap your expression in a conditional to get either than last 90 days, or the last 90-N days if there aren't a full 90 days. Something like

iif([Time.Monthly].[2013].[10].[3].Lag(90) IS NULL,
avg({[Time.Monthly].[2013].[10].[3].FirstSibling:[Time.Monthly].[2013].[10].[3]}),
avg({[Time.Monthly].[2013].[10].[3].Lag(90):[Time.Monthly].[2013].[10].[3]})
)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•