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 PBI Experts!
Update:
Seems that there's some bug or problem, re-opened the file today (nothing was changed, data wasn't refreshed) but the values reflect correctly this time
I have something I can't get my head around, hope someone can help
My Table consists of Version, Period, Item & Value where:
I am trying to create a table (see the lower table) whereby when a Period is selected, the table shows:
My attempt to create "Value_LatestWeek" that failed is:
Strangest thing is even though "LATEST_WEEK" correctly identifies as "Week 3", "Value_LatestWeek" adds up all values that belong to all the weeks
VersionPeriodItem Value
Forecast | Q1 2021 | Revenue | 45,000 |
Forecast | Q1 2021 | Cost of Materials | 37,500 |
Forecast | Sep FY 21 | Revenue | 15,000 |
Forecast | Sep FY 21 | Cost of Materials | 13,000 |
Forecast | Oct FY 21 | Revenue | 14,000 |
Forecast | Oct FY 21 | Cost of Materials | 12,500 |
Forecast | Nov FY 21 | Revenue | 16,000 |
Forecast | Nov FY 21 | Cost of Materials | 12,000 |
Week 1 | Q1 2021 | Revenue | 43,000 |
Week 1 | Q1 2021 | Cost of Materials | 37,000 |
Week 1 | Sep FY 21 | Revenue | 14,500 |
Week 1 | Sep FY 21 | Cost of Materials | 12,000 |
Week 1 | Oct FY 21 | Revenue | 13,500 |
Week 1 | Oct FY 21 | Cost of Materials | 13,000 |
Week 1 | Nov FY 21 | Revenue | 15,000 |
Week 1 | Nov FY 21 | Cost of Materials | 12,000 |
Week 2 | Q1 2021 | Revenue | 48,000 |
Week 2 | Q1 2021 | Cost of Materials | 42,000 |
Week 2 | Sep FY 21 | Revenue | 16,000 |
Week 2 | Sep FY 21 | Cost of Materials | 14,500 |
Week 2 | Oct FY 21 | Revenue | 17,000 |
Week 2 | Oct FY 21 | Cost of Materials | 15,000 |
Week 2 | Nov FY 21 | Revenue | 15,000 |
Week 2 | Nov FY 21 | Cost of Materials | 12,500 |
Working | Q1 2021 | Revenue | 42,500 |
Working | Q1 2021 | Cost of Materials | 37,000 |
Working | Sep FY 21 | Revenue | 15,500 |
Working | Sep FY 21 | Cost of Materials | 14,000 |
Working | Oct FY 21 | Revenue | 13,500 |
Working | Oct FY 21 | Cost of Materials | 12,000 |
Working | Nov FY 21 | Revenue | 13,500 |
Working | Nov FY 21 | Cost of Materials | 11,000 |
Solved! Go to Solution.
Hi,
Try this
Value_LatestWeek = CALCULATE([Values],FILTER(Data,Data[Version] = CALCULATE(MAX(Data[Version]),FILTER(Data,LEFT(Data[Version],4) = "WEEK"))))
Hi,
Try this
Value_LatestWeek = CALCULATE([Values],FILTER(Data,Data[Version] = CALCULATE(MAX(Data[Version]),FILTER(Data,LEFT(Data[Version],4) = "WEEK"))))
Thanks for your answer, seems like today when I open the file, the calculated value from my original file is reflecting the right numbers...
Not sure if there was some bug or memory problem on my PC? But your calculation works as well
You are welcome.
Appreciate any help!
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 |
---|---|
111 | |
96 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |