cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mzunino001
New Member

Average of 3 weeks without using time intellige functions

Hi there,

I'm trying to create a kind rolling average over the last 3 weeks but without using time intelligent functions.

 

My excel looks like this: 

 ABCDEFG
1 WEEK 1WEEK 2WEEK 3WEEK 4WEEK 5WEEK 6
2Observation / Total Items10%15%5%2%8%10%
3Last 3 Week average  10%7%5%7%

 

And the excel formula from D3 woud be =AVERAGE(B2:D2),  E3 =AVERAGE(C2:E2),  F3=AVERAGE(D2:F2), and G3=AVERAGE(E2:G2)

 

What would be the best way to do it in Power Bi?

 

My objetive is to use a "Line and clustered column chart" where my week is on X-Axis, my total Items as a Column y-axis and my Observations/Items as a Line y-axis

 

Just in case my data looks like this: 

Table 1 
DateWeek
1/1/20211
4/1/20211
7/1/20211
10/1/20212
13/1/20212
16/1/20212
19/1/20213
22/1/20213

(My tables are already related by date column)

Table 2  
DateItemsObservations
1/1/20217266
4/1/20219937
7/1/20218918
10/1/20218130
13/1/20218412
16/1/20217030
19/1/2021776
22/1/20218476

by the way, I'm using radom data just to give an example of what the data looks like.

 

Any suggestion would be greate...

 

1 ACCEPTED SOLUTION
jgeddes
Solution Sage
Solution Sage

If I understand correctly, you can create a table of averages

Averages Table =
SUMMARIZE(dimDate,dimDate[Week],"_averages",DIVIDE(SUM(factItems[Observations]),SUM(factItems[Items])))
and then create a measure from that table
Rolling 3 Week Average =
var _currentWeek =
SELECTEDVALUE(dimDate[Week])
var _firstWeek =
_currentWeek - 2
Return
IF(
    _firstWeek > 0,
    CALCULATE(
        AVERAGE('Averages Table'[_averages]),
        FILTER(all('Averages Table'), 'Averages Table'[Week] >= _firstWeek && 'Averages Table'[Week] <= _currentWeek)
    )
)

View solution in original post

6 REPLIES 6
jgeddes
Solution Sage
Solution Sage

If I understand correctly, you can create a table of averages

Averages Table =
SUMMARIZE(dimDate,dimDate[Week],"_averages",DIVIDE(SUM(factItems[Observations]),SUM(factItems[Items])))
and then create a measure from that table
Rolling 3 Week Average =
var _currentWeek =
SELECTEDVALUE(dimDate[Week])
var _firstWeek =
_currentWeek - 2
Return
IF(
    _firstWeek > 0,
    CALCULATE(
        AVERAGE('Averages Table'[_averages]),
        FILTER(all('Averages Table'), 'Averages Table'[Week] >= _firstWeek && 'Averages Table'[Week] <= _currentWeek)
    )
)

It's pretty close.

What I really need is the chart that I mention before more than the summarize table, so I modify your formula a little bit, and looks like this:

 
Rolling 3 Week Average
=
var _currentWeek =
SELECTEDVALUE(Table1[Week])
var _firstWeek =
_currentWeek - 2
Return
IF(
_firstWeek >= 0,
CALCULATE(
Calculos[% Observations/ Registers],
FILTER(all(Table1), Table1[Week] >= _firstWeek && Table1[Week] <= _currentWeek)
)
)
 
I did change _firstWeek >0 to _firstWeek >= 0
 
By the way, about Calculos[% Observations/ Registers]
Calculos is the table name (Spanish word of Calculations) and it is compose by the next measure:
 Measure: % Observations/ Registers = DIVIDE([Items],[Observations],0)
 
My current data is 
WEEK 0 = 100,00%
WEEK 1 =     5,33%
WEEK 2 =     4,19%
WEEK 3 =     7,80%
 
 
The average value for week 2 (week 0 to 2) is = 36,61% 
And The average value for week 3 (week 1 to 3) is = 5,77%
 
But what I'm getting the following results with your formula:
WEEK 2 = 46,94%
WEEK 3 = 5,64%  
 
I don't know why this difference is generated, maybe it is because I am using Calculos[% Observations/ Registers] as an expression, not sure at all.
 
Thanks for helping me...

It looks like you put the Calculos in the wrong table. 

Try, 

Make a new table with:

Calculos Table =
SUMMARIZE(Table1,Table1[Week],"_averages",[% Observations/ Registers])
Then make the following measure:
Rolling 3 Week Average =
var _currentWeek =
SELECTEDVALUE(Table1[Week])
var _firstWeek =
_currentWeek - 2
Return
IF(
    _firstWeek >= 0,
    CALCULATE(
        AVERAGE('Calculos Table'[_averages]),
        FILTER(all('Calculos Table'), 'Calculos Table'[Week] >= _firstWeek && 'Calculos Table'[Week] <= _currentWeek)
    )
)
You need the first table created in order to take the average of the 3 weeks in the measure. 

You are right...

Thanks for the help. I'm really glad

mzunino001
New Member

@parry2k  thanks for answering.

What I'm looking for is a DAX for the step 2.

Stept 1: Formula where I can calculate average of my observations over Items by week from my table 2 (Which I already have).

Stept 2: Then I need to calculate the average from the 3 last weeks for each week (Like the first excel table example).

 

I already have the next formula:

Measure 1: Observation/ Items = 
DIVIDE(Table2[Observations],Table2[Items],0)
 
Measure 2: % rolling average = AVERAGEX(FILTER(ALLSELECTED(Table1[Date]),Table1[Date]<=MAX(Table1[Date])), [ Observation/ Items])
 
My measure 2 It's an example of what I don't need but it's as far as I've come because it's a rolling average by day (this is what I mean of time intellingence functions, as far as I know it is a time intellingece function result). If I'm wrong about this measure, would be nice if you could correct me.
 
Hope this helps..
 
 

 

 

parry2k
Super User
Super User

@mzunino001 your question is not very clear, what do you mean by without time intelligence function also you want to use the last 3 weeks (what do you think week means here, is this not related to time? )

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.