Calculate Value based on Max & Criteria in Dimension

[Anonymous]

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:

• Each Period has multiple versions (e.g. Forecast, Week "X", Working)

I am trying to create a table (see the lower table) whereby when a Period is selected, the table shows:

• The corresponding value for Version = "Working"
• The corresponding value for the Latest Week (in this case "Week 3"), however I can't achieve this portion

My attempt to create "Value_LatestWeek" that failed is:

1. Create a measure to identify the latest week
   • LATEST_WEEK = CALCULATE(MAX(Table1[Version]),FILTER(Table1,LEFT(Table1[Version],4) = "WEEK"))
2. Create a measure to calculate value based on "LATEST_WEEK"
   • Value_LatestWeek = CALCULATE([Value],FILTER(Table1,Table1[Version] = [LATEST_WEEK] ))

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

[Ashish_Mathur · Accepted solution]

Hi,

Try this

Value_LatestWeek = CALCULATE([Values],FILTER(Data,Data[Version] = CALCULATE(MAX(Data[Version]),FILTER(Data,LEFT(Data[Version],4) = "WEEK"))))

[Anonymous]

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

[Ashish_Mathur]

You are welcome.

[Anonymous]

Appreciate any help!