Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I've been searching over and over, but I can't find the solution - so I'm afraid that there isn't any solution to this.
I have a table ("Time reports") with all reporting for our employes, grouped by Person and Date. The table looks like this example:
Date | Person | Billed hours |
2017-04-12 | Joe | 5 |
2017-04-13 | Joe | 8 |
2017-04-13 | Chris | 8,5 |
2017-04-13 | Dave | 6 |
2017-04-13 | Martin | 5 |
2017-04-14 | Chris | 7 |
2017-04-14 | Dave | 4 |
2017-04-14 | Martin | 5 |
2017-04-15 | Joe | 8 |
2017-04-15 | Chris | 8 |
2017-04-15 | Dave | 6 |
2017-04-16 | Martin | 9 |
2017-04-17 | Joe | 8 |
2017-04-17 | Chris | 8 |
2017-04-17 | Dave | 6 |
2017-04-17 | Martin | 5 |
I want to calculate the average of hours/day for each person individually. The average should be over the latest 10 reported days, per person.
Every person doesn't have reports for every day, so the latest 10 reported days won't be the same for every person.
In any other language I would have accompliced this by looping through the persons and count 10 days from a descending list - but how do I do in DAX or Power Query!?
One thought was to calculate this in the SQL-server - BUT, the table is merged from multiple data sources (Both SQL and Excel since we have multiple systems for time reporting) and each person can occur in both data sources.
Solved! Go to Solution.
Assuming you only have the 'Person' on rows, then while i have not tested it this should work:
Avg per Person last 10 reported days =
CALCULATE ( AVERAGE ( 'Time Reports'[Billed Hours] ); TOPN ( 10, VALUES ( 'Time Reports'[Date] ); LASTDATE ( 'Time Reports'[Date] ) ) )
Assuming you only have the 'Person' on rows, then while i have not tested it this should work:
Avg per Person last 10 reported days =
CALCULATE ( AVERAGE ( 'Time Reports'[Billed Hours] ); TOPN ( 10, VALUES ( 'Time Reports'[Date] ); LASTDATE ( 'Time Reports'[Date] ) ) )
AWESOME! Thank you - you just turned my **bleep**ty day into a really great day!!
I created a new table with only the persons and added a calculaded column with your code - worked like a charm!
Next step for me is to analyze the code so I can understand how it works
Have a great day everyone!
Hi @simonfalun,
I just verified that the formula provided by @mattbrice above should work in your scenario.
@mattbrice, nice solution! Kudos +1.
Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |