Moving Average of a time series

[ekambulow]

I am quite new to Power Bi.  I have a table that has 3 relevant columns:  Name, Values, and Time (sampled in seconds - not date tiime, so the precanned daily moving average methods don't work).  I'd like to create a moving average column which Groups by Name and then does a moving average for the Values.  I current have Scatter Charts that plot the Values vs Time  with the seelected Page Filtered Name as the legend.  I would like to instead plot the smoothed Values. 

How do I create a new column that does that.

[ekambulow]

This may highlight may inexperience with Queries - but I'll say what I uderstand.  When I create the original Power query,  I concanetated them.  My assumption is that it would maintain its order.  There is a colum of RunTime for each CSV.    I've just added an Index Column to the Power Query of the concanated CSVs -so now assuming the original temporal order is maintained the index should maintain the temperoral order.   I don't care what order the CSVs are, just that that runtime for each CSV is maintained (I will be comparing Values between RunTimes starting at 0 for each csv).    My plan was to build a Table/Column using DAX manipulating the original database PowerQuery. 

[lbendlin]

 My assumption is that it would maintain its order. 

That would be nice, wouldn't it?  Alas, there is no guarantee. 

Anyway, Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[ekambulow]

This is a sample of the results that I am looking for.  I used a simple 3 point averaging.

Index	Filename	RunTime	Value	Smoothed
1	First.CSV	0	0.1	2.7
2	First.CSV	1	3	4
3	First.CSV	2	5	4.5
4	First.CSV	3	4	4
5	2nd.CSV	0	0.3	0.266667
6	2nd.CSV	1	0.2	0.433333
7	2nd.CSV	2	0.3	0.4
8	2nd.CSV	3	0.8	0.4
9	2nd.CSV	4	0.1	0.333333
10	2nd.CSV	5	0.3	0.45
11	2nd.CSV	6	0.6	0.6
12	3rd.CSV	0	0.4	0.5
13	3rd.CSV	1	0.3	0.433333
14	3rd.CSV	2	0.8	0.633333
15	3rd.CSV	3	0.2	0.6
16	3rd.CSV	4	0.9	0.9
17	3rd.CSV	5	0.7	1.266667
18	3rd.CSV	6	1.1	2.366667
19	3rd.CSV	7	2	5.666667
20	3rd.CSV	8	4	7.5
21	3rd.CSV	9	11	11

What I am looking for is to be able to create a visual that will look like this:

[lbendlin · Accepted solution]

Please define "simple" 🙂  I used a -1 to +1 window, you can change it to -2 to 0 (for example).

Smoothed = 
var w = WINDOW(-1,REL,1,REL,ALLSELECTED(Table2[RunTime]),ORDERBY(Table2[RunTime]))
return CALCULATE(average(Table2[Value]),w)

[ekambulow]

Have a related problem now.  
I use these function to try to normalize the results (and I have created a maxTable with a Many to 1 relationship).  I now get a circular reference errror. How should I approach this problem not to get this problem.

MaxTable= SUMMARIZECOLUMNS('Table2'[Filename],"V MAX",MAX('Table2'[Smoothed]))
NormV= 'Table2'[Smoothed] /RELATED(MaxTable[V MAX])

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[ekambulow]

I think the information provide fully defines the problem - althought I don't know how to verify.  The simulated table provide above is Table 2.   I implemented your Smoothed function.  And then added MaxTable (based on Table 2) and a new  NormV column in Table 2 (along with adding the associated relationship) - I then go the error.   The only difference (that I am aware) between Table 2 and the real data is the size.

[lbendlin]

Can you post a sample PBIX that illustrates the issue?

[ekambulow]

Wow.  Thank you.  That is so amazingly simple!

[ekambulow]

Currently no, but I can add one to the main query (which is a concatenation of time series csvs) if that would help.  We can call that new column Index for the sake of this example.

[lbendlin]

Let me rephrase. How are you guaranteeing the temporal order of your data points?

[lbendlin]

Does your data have an index or row number column?