cancel
Showing results 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

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

1 ACCEPTED SOLUTION
Super User

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

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

Rank column should be week start or end date.

Thanks for pointing out the mistake.

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

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

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

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

Super User

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.

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

Super User

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

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

Rank column should be week start or end date.

Thanks for pointing out the mistake.

Community Support

hi  @Anonymous

For your case, you could try this way:

Step1:

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)``

Step2:

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])))``````

Result:

and here is sample pbix file, please try it.

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

Regards,

Lin

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.