Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Making a moving average backwards

Hello everyone!
I Have the follow problem, I want to do a moving average backwards.
for example, as the table below:

DateValue
01/01/201910
02/01/201911
03/01/201914
04/01/201915
05/01/201918
06/01/201919
07/01/201918
08/01/201920
09/01/201914
10/01/201925


So what I want to show is: On date 10/01/2019, the value that must show is 25. 
On day 09/01/2019, it must show the average between day 10 and 9, given me a total of 19,5  (That is (20+14)/2)
On day 08/01, it must show an average of the days 10, 9 and 8, given me a total of 19,6 (That is (20+14+25)/3)
On day 07/01 must show the average of the day 10,9,8,7 and so on....

How can I do this?

Thank you!

1 ACCEPTED SOLUTION
v-cherch-msft
Employee
Employee

Hi @Anonymous 

You may create a measure like below:

Measure =
DIVIDE (
    CALCULATE (
        SUM ( Table2[Value] ),
        FILTER ( ALL ( Table2 ), Table2[Date] >= MAX ( Table2[date] ) )
    ),
    COUNTROWS ( FILTER ( ALL ( Table2 ), Table2[Date] >= MAX ( Table2[date] ) ) )
)

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
smpa01
Super User
Super User

let
    Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Desktop/Making-a-moving-average-backwards/m-p/649510#M311441")),
    Data0 = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Value", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
    Custom1 = List.Skip(List.Accumulate(#"Added Index"[Value],{0},(state,current)=> state&{List.Last(state)+current})),
    Custom2 = Table.FromColumns(Table.ToColumns(#"Added Index")&{Custom1}),
    #"Inserted Division" = Table.AddColumn(Custom2, "Moving Average Backwards", each [Column4] / [Column3], type number),
    #"Sorted Rows1" = Table.Sort(#"Inserted Division",{{"Column3", Order.Descending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Column3", "Column4"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Date"}, {"Column2", "Value"}})
in
    #"Renamed Columns"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
v-cherch-msft
Employee
Employee

Hi @Anonymous 

You may create a measure like below:

Measure =
DIVIDE (
    CALCULATE (
        SUM ( Table2[Value] ),
        FILTER ( ALL ( Table2 ), Table2[Date] >= MAX ( Table2[date] ) )
    ),
    COUNTROWS ( FILTER ( ALL ( Table2 ), Table2[Date] >= MAX ( Table2[date] ) ) )
)

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.