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.
Hi all,
What I'm trying to do is average [ACT Productivity] with matching [Code] for all dates less that or equal to the date for the selected item. Here's my data, where [**bleep** Productivity] is Is shown calculated correctly. I can't seem to get PBI to do this:
[Date Week Ending] [Code] [ACT Productivity] [**bleep** Productivity]
1/24/2021 0:00 | 3.8602 | 1.309642857 | 1.309642857 |
2/14/2021 0:00 | 3.8602 | 10.88947368 | 6.099558269 |
1/9/2022 0:00 | 3.7112 | 2.525 | 2.525 |
1/16/2022 0:00 | 3.7112 | 3.833333333 | 3.179166667 |
2/27/2022 0:00 | 3.7122 | 9.814285714 | 9.814285714 |
3/13/2022 0:00 | 3.7452 | 4.0453125 | 4.0453125 |
3/20/2022 0:00 | 3.7452 | 4.633082707 | 4.339197604 |
4/17/2022 0:00 | 3.7112 | 2.192 | 2.850111111 |
4/17/2022 0:00 | 3.7122 | 11.03333333 | 10.42380952 |
4/17/2022 0:00 | 3.8112 | 2.459504132 | 2.459504132 |
4/24/2022 0:00 | 3.7112 | 1.563948498 | 2.528570458 |
4/24/2022 0:00 | 3.7122 | 11.35 | 10.73253968 |
5/1/2022 0:00 | 3.7112 | 2.818930041 | 2.586642374 |
5/1/2022 0:00 | 3.7122 | 3.04 | 8.809404761 |
5/1/2022 0:00 | 3.8112 | 1.369369369 | 1.914436751 |
5/8/2022 0:00 | 3.7112 | 1.905336427 | 2.473091383 |
5/8/2022 0:00 | 3.7122 | 3.536585366 | 7.754840882 |
5/8/2022 0:00 | 3.8112 | 1.275 | 1.701291167 |
5/8/2022 0:00 | 3.8122 | 2.2 | 2.2 |
5/15/2022 0:00 | 3.7112 | 3.393125 | 2.604524757 |
5/15/2022 0:00 | 3.7112 | 3.88125 | 2.764115412 |
5/15/2022 0:00 | 3.7122 | 9.416058394 | 8.031710467 |
5/15/2022 0:00 | 3.7142 | 8.360294118 | 8.360294118 |
5/15/2022 0:00 | 3.8112 | 2.516129032 | 1.905000633 |
5/15/2022 0:00 | 3.8112 | 2.541935484 | 2.032387603 |
5/15/2022 0:00 | 3.8122 | 4.6 | 3.4 |
5/22/2022 0:00 | 3.7112 | 2.75862069 | 2.763504888 |
5/22/2022 0:00 | 3.7122 | 10.17 | 8.337180401 |
5/22/2022 0:00 | 3.7142 | 8.888291747 | 8.624292933 |
5/22/2022 0:00 | 3.8112 | 4.490909091 | 2.442141185 |
5/22/2022 0:00 | 3.8122 | 4.675 | 3.825 |
5/22/2022 0:00 | 3.8142 | 8.620833333 | 8.620833333 |
Here's the measure I've got:
And here's a snip of the result. As you can see, [**bleep** Productivity] is coming through exactly the same as [ACT Productivity]:
What am I doing wrong?
Solved! Go to Solution.
please try
**bleep** Productivity =
VAR MaxDate =
MAX ( 'Date'[Date] ) -- Saves the last visible date
RETURN
CALCULATE (
AVERAGE ( data[ACT Productivity] ),
-- Average productivity
'Date'[Date] <= MaxDate,
-- Where date is before the last visible date
ALLEXCEPT ( data, data[Code] ),
-- Where code is the same as this line
ALL ( Date ) -- Removes any other filters from Date
)
Thanks, tamerj1! That did it!
please try
**bleep** Productivity =
VAR MaxDate =
MAX ( 'Date'[Date] ) -- Saves the last visible date
RETURN
CALCULATE (
AVERAGE ( data[ACT Productivity] ),
-- Average productivity
'Date'[Date] <= MaxDate,
-- Where date is before the last visible date
ALLEXCEPT ( data, data[Code] ),
-- Where code is the same as this line
ALL ( Date ) -- Removes any other filters from Date
)
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |