Moving Average Based on Slicer Value

[Nyelugo]

Hi,

I've built a report based on an Excel data-source without a date column. However, week numbers are captured manually in this source.

Challenge is that I'm unable to create a moving average measure (4-week average in this case) of a column named "Depletion Qty", based on a single-selection slicer that selects values from the "Week" column. For example, if I select "Week 10" from the slicer, I want the measure to calculate a "4-week average of Depletion Qty", spanning weeks 7, 8, 9 & 10.

Any help on this will be appreciated.

[v-alq-msft]

Hi, @Nyelugo 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft]

Hi, @Nyelugo 

Based on your description, I created data to reproduce your scenario.

Table:

You may create a measure as below.

Avg of Qty = 
var _selectedweeknum = SELECTEDVALUE('Table'[Weeknum])
return
IF(
    HASONEVALUE('Table'[Weeknum]),
    DIVIDE(
        CALCULATE(
            SUM('Table'[Qty]),
            FILTER(
                ALL('Table'),
                'Table'[Weeknum]>=_selectedweeknum-3&&
                'Table'[Weeknum]<=_selectedweeknum
            )
        ),
        4
    ),
    0
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vivran22 · Accepted solution]

Hello @Nyelugo ,

You may try this:

Add an index column using Power Query

Use this Measure:

Moving Avg = 
VAR _MaxWeek = MAX(dtTable[Index])
VAR _NumWeeks = 4
VAR _PeriodToUse = 
    FILTER(
        ALL(dtTable),
        AND(
            dtTable[Index] > (_MaxWeek - _NumWeeks),
            dtTable[Index] <= _MaxWeek
        )
    )
VAR _MovingAvg = 
    IF(
        COUNTROWS(_PeriodToUse) > 3,
    CALCULATE(
        AVERAGEX(dtTable,dtTable[Value]),
        _PeriodToUse
    )
    )
RETURN
_MovingAvg

Following is the output:

Sample pbix file here

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[amitchandak]

@Nyelugo 

Please check my file. It uses Week rank to deal with it. I have Rolling/last 12 weeks. You can change the same for 4 weeks

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

[Nyelugo]

Hi Amit,

Sorry it's taken a while for me to revert. It seems your file is no longer available for download. Could you re-upload?

[Greg_Deckler]

So if your week number column is numeric, you should be able to do this:

Measure =
VAR __Week = SELECTEDVALUE('Table'[Week])
VAR __LastWeek = __Week - 4 + 1
RETURN
AVERAGEX(FILTER(ALL('Table'),[Week] >= __LastWeek && [Week] <= __Week),[Depletion Qty])

There is a good bet that all of the assumptions I made are flat out wrong.