Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
Solved! Go to 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.
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 @
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.
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:
and here is sample pbix file, please try it.
By the way, from your sample pbix file, your expected output seems wrong.
Regards,
Lin
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
80 | |
65 | |
52 | |
49 |
User | Count |
---|---|
211 | |
87 | |
80 | |
69 | |
60 |