Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
newlearnpbi123
Frequent Visitor

How to calculate rolling 5 week average ?

I have only date column which contains weeks, now i want to calculate rolling 5 week average,
i tried dateinperiod could not get it to work.

thank you

 

@PaulDBrown 

1 ACCEPTED SOLUTION

Hi @Sergii24 ,thanks for the quick reply, I'll add further.

Hi @newlearnpbi123 ,

The Table data is shown below:

vzhouwenmsft_0-1715753778848.png

Please follow these steps:
1. Use the following DAX expression to create a column

Weeknumber = WEEKNUM([Date],2)

vzhouwenmsft_1-1715753825442.png

2. Use the following DAX expression to create a measure

Measure = 
VAR _a = SELECTEDVALUE('Table'[Weeknumber])
VAR _b = CALCULATE(AVERAGE('Table'[Unit]),FILTER(ALL('Table'),'Table'[Weeknumber] <= _a && 'Table'[Weeknumber] >= _a -4 ))
RETURN _b

3.Final output

vzhouwenmsft_2-1715753903258.png

 

View solution in original post

5 REPLIES 5
Sergii24
Super User
Super User

You can upload sample pbix used by for the example here. If same calculation provides blank(), than there is something in your semantic model that should be taken into account. However, I hope that by having this pbix as example and making research online you can find the answer! If not, please share your pbix sample with your semantic model.

newlearnpbi123_0-1715681921186.png

sorry i cannot upload the pbi file for the obvious reasons,
i just have 3 column one is category and other is weeks and other is unit ( above screenshot )

any help appreciated sergii, thank you.

 

Hi @Sergii24 ,thanks for the quick reply, I'll add further.

Hi @newlearnpbi123 ,

The Table data is shown below:

vzhouwenmsft_0-1715753778848.png

Please follow these steps:
1. Use the following DAX expression to create a column

Weeknumber = WEEKNUM([Date],2)

vzhouwenmsft_1-1715753825442.png

2. Use the following DAX expression to create a measure

Measure = 
VAR _a = SELECTEDVALUE('Table'[Weeknumber])
VAR _b = CALCULATE(AVERAGE('Table'[Unit]),FILTER(ALL('Table'),'Table'[Weeknumber] <= _a && 'Table'[Weeknumber] >= _a -4 ))
RETURN _b

3.Final output

vzhouwenmsft_2-1715753903258.png

 

Sergii24
Super User
Super User

Hi @newlearnpbi123, here is an easy method:

Rolling average = 
VAR _NumberOfPeriods = 2                                    //change it to 5 or any other number
VAR _MinPeriod = MIN( 'Table'[Week] )                       //get the minimum week for current filter context
VAR _NumPreviousWeeks =                                     //this measure is required to start calcualtion only when you get last N weeks
    COUNTROWS(
        CALCULATETABLE(
            VALUES( 'Table'[Week] ),
            'Table'[Week] < _MinPeriod
        )
    )
RETURN
    IF(
        _NumPreviousWeeks < _NumberOfPeriods,
        BLANK(),
        CALCULATE(
            AVERAGE( 'Table'[Value] ),                          //calculate the average
            'Table'[Week] >= _MinPeriod - _NumberOfPeriods,     //define min week
            'Table'[Week] < _MinPeriod                          //define max week
        )
    )


Here is the output:

Sergii24_0-1715678120143.png


Feel free to adjust formula parameters as needed.

Good luck!

 

thank you sergii, but i am getting blank

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors