cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nha
Frequent Visitor

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 

1 ACCEPTED SOLUTION

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. 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

6 REPLIES 6
amitchandak
Super User IV
Super User IV

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 @



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Nha
Frequent Visitor

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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Nha
Frequent Visitor

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. 

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. 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

v-lili6-msft
Community Support
Community Support

hi  @Nha 

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:

1.JPG

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors