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

E

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"
Sort by date Sort by votes
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
JEC said:
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
Click to expand...
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
You must log in or register to reply here.

Similar threads

RICH937
  • Question
How to create Running Totals on multiple non-date values and conduct filtered analysis on the results. Which Power Tool is the more efficient choice?
Replies
3
Views
263
RICH937
RICH937
TheMacroNoob
  • Question
Rank by Group - Excluding w/ Criteria
Replies
0
Views
340
TheMacroNoob
TheMacroNoob
T
  • Question
Power BI changes in status between two periods
Replies
0
Views
318
Tashat
T
S
  • Solved
Power BI Line chart visual
Replies
3
Views
275
shansakhi
S
V
  • Question
I need to display Average of a calculated column on basis of month wise data
Replies
0
Views
380
vishalpat13
V

Forum statistics

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

Share this page

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