Calculate value based on conditions

[tonyclifton]

Hello community,

I've been trying for hours now with below messed up data to build a measure that will calculate the total cost for each stage:

MyMeasure = CALCULATE (
	MAX('Table'[Total Cost]), 
	FILTER(ALLSELECTED('Table'[Project] ), 'Table'[Project] = 'Table'[Project] )
)

MyMeasure works as expected for below example but my problem is that I only get the MAX value per Stage when in reality it should be the value where EndDate and ValueExistsForDate/YearMonth are closest or a value in column Cost per Stage exists.

I hope you can help me out here.

Thank you very much.

Here's the raw sample data:

Project	Stage	YearMonth	Cost per Stage	Total Cost	End Date	ValueExistsForDate
ABC	Stage 1	2019-08	322.362	322.362	2019-08-30	2019-08-01
ABC	Stage 1	2019-08			2020-03-27
ABC	Stage 1	2019-08			2021-07-01
ABC	Stage 1	2019-08			2022-06-10
ABC	Stage 1	2019-08			2023-04-07
ABC	Stage 1	2019-08			2023-09-22
ABC	Stage 2	2020-03			2019-08-30
ABC	Stage 2	2020-03	322.362	322.362	2020-03-27	2020-03-01
ABC	Stage 2	2020-03			2021-07-01
ABC	Stage 2	2020-03			2022-06-10
ABC	Stage 2	2020-03			2023-04-07
ABC	Stage 2	2020-03			2023-09-22
ABC	Stage 3	2021-07			2019-08-30
ABC	Stage 3	2021-07		322.362	2020-03-27	2020-03-01
ABC	Stage 3	2021-07	359.088	359.088	2021-07-01	2021-07-01
ABC	Stage 3	2021-07			2022-06-10
ABC	Stage 3	2021-07			2023-04-07
ABC	Stage 3	2021-07			2023-09-22
ABC	Stage 4	2022-06			2019-08-30
ABC	Stage 4	2022-06		322.362	2020-03-27	2020-03-01
ABC	Stage 4	2022-06		359.088	2021-07-01	2021-07-01
ABC	Stage 4	2022-06	400.089	400.089	2022-06-10	2022-06-01
ABC	Stage 4	2022-06			2023-04-07
ABC	Stage 4	2022-06			2023-09-22
ABC	Stage 5	2023-04			2019-08-30
ABC	Stage 5	2023-04		322.362	2020-03-27	2020-03-01
ABC	Stage 5	2023-04		359.088	2021-07-01	2021-07-01
ABC	Stage 5	2023-04		400.089	2022-06-10	2022-06-01
ABC	Stage 5	2023-04	400.089	400.089	2023-04-07	2023-04-01
ABC	Stage 5	2023-04			2023-09-22
ABC	Stage 6	2023-09			2019-08-30
ABC	Stage 6	2023-09		322.362	2020-03-27	2020-03-01
ABC	Stage 6	2023-09		359.088	2021-07-01	2021-07-01
ABC	Stage 6	2023-09		400.089	2022-06-10	2022-06-01
ABC	Stage 6	2023-09		400.089	2023-04-07	2023-04-01
ABC	Stage 6	2023-09	421.583	421.583	2023-09-22	2023-09-01

[v-zhangti · Accepted solution]

Hi, @tonyclifton 

You can try the following methods.

Measure = 
Var _maxdate=CALCULATE(MAX('Table'[End Date]),FILTER(ALLEXCEPT('Table','Table'[Project],'Table'[Stage]),[Total Cost]<>BLANK()))
Return
CALCULATE(MAX('Table'[Total Cost]),FILTER(ALLEXCEPT('Table','Table'[Project],'Table'[Stage]),[End Date]=_maxdate))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tonyclifton]

Awesome - works as expected. Took me while to get it working in my real environment but everything looks good now.
Thank you very much.

[tonyclifton]

Thank you very much for the response. I will get back to you tomorrow as I'm currently not able to test with my data.