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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 

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. 

 

View solution in original post

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

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.

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.

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. 

 

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:

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.