Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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:    A B C D E F G 1   WEEK 1 W...
  • jgeddes's avatar
    jgeddes
    4 years ago

    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)
        )
    )
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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..