Help on calculating moving average using DAX

[kaka20461977]

I am new to PowerPivot and DAX functions and I want to calculate a moving average of a variable in last 5 minutes. Can anybody shed some light on this? I attached a screenshot to show the data structure. Data Structure.png

[kaka20461977]

Anybody can help on this?

I am new to PowerPivot and DAX functions and I want to calculate a moving average of a variable in last 5 minutes. Can anybody shed some light on this? I attached a screenshot to show the data structure. Attachment 270224

[Richard Buttrey]

Hi,

The lack of response is no doubt because you chose to upload a picture rather than your workbook (contrary to the forum guidance in the rules)

No one is inclined to recreate your data for testing purposes when you have it to hand. So upload the workbook, manually add your calculated results, or at lease a sample of them, tell us which sheets/cells are the results as opposed to original data and how you have arrived at the result unless it's blindingly obvious.

[kaka20461977]

Thanks for your kind reminder! I attached the data file. Hopefully, I can get the answer soon.

Hi,

The lack of response is no doubt because you chose to upload a picture rather than your workbook (contrary to the forum guidance in the rules)

No one is inclined to recreate your data for testing purposes when you have it to hand. So upload the workbook, manually add your calculated results, or at lease a sample of them, tell us which sheets/cells are the results as opposed to original data and how you have arrived at the result unless it's blindingly obvious.

[oeldere]

The sheet is empty, so with this sheet, you get as much help as before.

[kaka20461977]

It's not empty. See the file size (278.5kb). Please go click 'POWERPIVOT', then click 'Manage', then you will see the data. As I mentioned earlier, I'm pretty new to powerpivot, I appreciate everybody's time and help!!

The sheet is empty, so with this sheet, you get as much help as before.

[Richard Buttrey]

Hi,

If your data is always at minute intervals and always in column B then

=AVERAGE(OFFSET(B1,MATCH(0,B:B,-1)-5,0,5,1))

[oeldere]

Why don't you try it !

If i open your file i get an empty sheet with a few cells collored yellow.

[kaka20461977]

Thanks for your reply!!! If I know how to do it, why did I ask for help here?

Why don't you try it !

If i open your file i get an empty sheet with a few cells collored yellow.

[oeldere]

Why don't you try it !

Your respond is not what I meant.

I meant: Why don't you open your file, that you posted (and see what is in it).

Then you will see that it is empty.

How do you expect to get good help (from us), if you don't add an decent excel file to work with.

[AlKey]

I do have PowerPivot installed and I can't see any data. The data is there I suppose however it is not accessible.

[kaka20461977]

I don't know why you cannot access the data. I attached another file and copied and pasted the data.

I do have PowerPivot installed and I can't see any data. The data is there I suppose however it is not accessible.

[Izandol]

This measure should work for your pivot table:
=if(countrows(values(PensacolaCH1[TIMESTAMP])) = 1,
CALCULATE(
AVERAGEX(VALUES(PensacolaCH1[TIMESTAMP]), PensacolaCH1[Sum of CHW_LT])
,PensacolaCH1[TIMESTAMP] <= VALUES(PensacolaCH1[TIMESTAMP]) && PensacolaCH1[TIMESTAMP] > VALUES(PensacolaCH1[TIMESTAMP])-TIME(0,5,0)
)
, blank())

[krasstrass]

Hey Izandol,

I am searching for the same issue. Unfortunately the formular results in blank cells.
Attachment 286283
dax.PNG

I do not get how to change this formula to receive a value

Format:
symbol nr timestamp open high low close volume
GDAXI 1 18.10.2011 09:01 5789,29 5789,29 5789,29 5789,29 0
GDAXI 2 18.10.2011 09:02 5788,8101 5789,5801 5783,54 5787,6299 0
...
But time intervall is not constant so only the last 3 rows should be used for an average

[Izandol]

This formula is for measure not for calculated column.