Convert Excel Formula to DAX Which Averages Last 2 Values Until Certain Period of Time

[ExcelToDAX]

I have the following table in Excel:

DATE	VALUE
1/1/2024​	100.00
2/1/2024​	150.00
3/1/2024​	200.00
4/1/2024​
5/1/2024​
6/1/2024​
7/1/2024​
8/1/2024​
9/1/2024​
10/1/2024​
11/1/2024​
12/1/2024​

Lets say this table is called Table1. For the Months 4/1/2024 - 12/1/2024, I want to average the previous two month values. For Example: 4/1/2024 would be 175 (200 + 150), and 5/1/2024 would be 187.5 (200 + 175). I created this function to solve the Excel formula:

=REDUCE(FILTER(Table1[VALUE],Table1[VALUE]<>""),FILTER(Table1[VALUE],Table1[VALUE]=""),LAMBDA(a,b,VSTACK(a,AVERAGE(TAKE(a,-2)))))

If I wanted to achieve this in Power BI DAX, how could I solve the problem?

 

[ExcelToDAX]

Also posted here

 

[JEC]

You should do it in the Power Query Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcrLCQAhEATRXOasMN1+do1FzD8NWdhR6GM9ak5DRqazWjK420of8VALKkE8Vw36u0l36Uf6lR7ScAUo8MLa", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, VALUE = _t]),
    types = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"VALUE", Int64.Type}}),
    rollAvg = List.Accumulate(types[VALUE],{},(s,c)=> if c <> null then s & {c} else s & {Number.Round(List.Average(List.LastN(s,2)),1)}),
    result = Table.FromRows(List.Zip({types[DATE],rollAvg}),{"DATE", "VALUE"})
in
    result

Starting point

Result

 

[ExcelToDAX]

You should do it in the Power Query Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcrLCQAhEATRXOasMN1+do1FzD8NWdhR6GM9ak5DRqazWjK420of8VALKkE8Vw36u0l36Uf6lR7ScAUo8MLa", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, VALUE = _t]),
    types = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"VALUE", Int64.Type}}),
    rollAvg = List.Accumulate(types[VALUE],{},(s,c)=> if c <> null then s & {c} else s & {Number.Round(List.Average(List.LastN(s,2)),1)}),
    result = Table.FromRows(List.Zip({types[DATE],rollAvg}),{"DATE", "VALUE"})
in
    result

Starting point
View attachment 108376

Result
View attachment 108377

Thank you! This works for this specific scenario. But I need to use DAX because I need to have a slicer that can remove any outliers. For example, instead of going back just 2 months, if we were to go back 8 months and I want to remove a certain month from that calculation, I won't be able to do this in Power Query.

 

[JEC]

I am afraid you can’t do this with DAX. There is no function to fill these empty cells because within the function there should be some reference to earlier averages in memory. That is wat the accumulate and lamba do.

If you already had all the data availabe, it would be possible.

But, maybe someone can surprise and prove me wrong

 

[ExcelToDAX]

That's what I figured as well unfortunately. Like you said, hopefully someone blows our mind and figures it out! Thanks again for the help!