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

ExcelToDAX

Board Regular
Joined
Feb 9, 2023
Messages
169
Office Version
  1. 365
Platform
  1. Windows
I have the following table in Excel:


DATEVALUE
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:
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?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
You should do it in the Power Query Editor.

Power Query:
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
1710438622927.png


Result
1710438722168.png
 
Upvote 0
You should do it in the Power Query Editor.

Power Query:
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.
 
Upvote 0
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🙂
 
Upvote 0
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! :)
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top