Skip to main content
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

Not applicable

Calculate the average working hours per week for the last 16 weeks dependent on filter slicer

I need to calculate the average amount of working hours an employee has had per week over a period of the past 16 weeks.


Futhermore, it shoud be possible to select a specific week and then go back in time and see the average amount of working hours per week for the past 16 weeks for that selected week.


In the sample data the average amount would be 52,5 hours if I choose week 8 in Power BI.

If I choose week 7 then the average amount should be 51,40.


Sample pbix file 


Seem like at some time during experiment I changed rank from week start date to week num. Please check the file now.


Rank column should be week start or end date.


Thanks for pointing out the mistake. 


View solution in original post

Super User
Super User

To work with week, please refer to my file. This uses dates and week rank to work with no week.

To take Avg, Prefer to divide it count distinct of employees or week.


If this does work for you.Can you share sample data and sample output. Mark me @

Not applicable

Hi amitchandak,


I need to be able to select e.g. week 2020-07 and take the sum of the last 16 weeks (period 2019-44 to 2020-07). It looks like your measure 'Last 12 week Sales' starts from 0 every year.

You make 12 as 16 it should work. The reason I used rank, so that it does not reset at year

Last 16 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-16 && 'Date'[Week Rank]<=max('Date'[Week Rank])))


As long as you keep your week in date table it should work fine. If select a week and display in the card or not time view it should show 16 weeks of data.

Not applicable

The sum of the last 12 weeks if week 2020-07 is selected should be 1,677,564 for your dataset. 
Your Power Bi shows 1,031,461 for week 2020-07 since it starts from week 2020-01. See this picture  it can maybe explain what I mean.


I need a measure that calculates the rolling 16 week back from the week the user select in the filter slicer.

Seem like at some time during experiment I changed rank from week start date to week num. Please check the file now.


Rank column should be week start or end date.


Thanks for pointing out the mistake. 


hi  @Anonymous 

For your case, you could try this way:


Add a yearweek column and a rank column based on yearweek column.

Yearweek = YEAR([Date])*100+Ark1[Week no]
Yearweek rank = RANKX(Ark1,[Yearweek],,ASC,Dense)


Create a last16weeks measure by this logic

Last 16weeks sum = CALCULATE(SUM(Ark1[Amount]),FILTER(ALL(Ark1),Ark1[Yearweek rank]>=MAX(Ark1[Yearweek rank])-17&&Ark1[Yearweek rank]<=MAX(Ark1[Yearweek rank])))
Last 16weeks avg = 
AVERAGEX(CALCULATETABLE(VALUES(Ark1[Yearweek]),FILTER(ALL(Ark1),Ark1[Yearweek rank]>=MAX(Ark1[Yearweek rank])-17&&Ark1[Yearweek rank]<=MAX(Ark1[Yearweek rank]))),CALCULATE(SUM(Ark1[Amount])))



and here is sample pbix file, please try it.

By the way, from your sample pbix file, your expected output seems wrong.




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

Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.