topic Re: Return Value by max date where value is not equal to value of max date by dimension. in DAX Commands and Tips

Return Value by max date where value is not equal to value of max date by dimension.

ArpitVijay — Sun, 23 Jul 2023 21:06:15 GMT

I would like to return "Value2" by max date and by Category "Name"

and then return the respective value from column "Value1"

I also want to compare the Value2 column max date values and return

the non-similar values by the max date concept by category.

and then return the respective value from column "Value1"

The output should be like this

Name	DDMMYY	Value1	Vaue2
A	1/1/2023	5	2
A	1/2/2023	5	2
A	1/3/2023	5	2
A	1/4/2023	6	3
A	1/5/2023	8	5
A	1/6/2023	8	6
A	1/7/2023	8	6
B	1/1/2023	7	3
B	1/2/2023	7	3
B	1/3/2023	7	3
B	1/4/2023	8	4
B	1/5/2023	10	6
B	1/6/2023	10	7
B	1/7/2023	10	7

Re: Return Value by max date where value is not equal to value of max date by dimension.

Manoj_Nair — Sun, 23 Jul 2023 23:44:02 GMT

ArpitVijay- Please take a look at this DAX measure and let me know if it works for you. I'm uncertain if the results displayed by you are accurately calculated. Do let me know if there's something I might have overlooked.

VALUE X = VAR MaxDate = CALCULATE(MAX('Table'[DDMMYY]), ALL('Table')) VAR MaxValue2BeforeLastDate = CALCULATE(MAX('Table'[Vaue2]), 'Table'[DDMMYY] <> MaxDate) RETURN MaxValue2BeforeLastDate VALUE Y = VAR MaxDate = CALCULATE(MAX('Table'[DDMMYY]), ALL('Table')) VAR MaxValue1BeforeLastDate = CALCULATE(MAX('Table'[Value1]), 'Table'[DDMMYY] <> MaxDate) RETURN MaxValue1BeforeLastDate

Re: Return Value by max date where value is not equal to value of max date by dimension.

tamerj1 — Mon, 24 Jul 2023 04:53:45 GMT

Hi ArpitVijay
It is nor very clear and the sample data might be a little misleading. Howeve, please refer to attached sample file with the proposed solution

Value X = SUMX ( VALUES ( 'Table'[Name] ), MAXX ( TOPN ( 1, CALCULATETABLE ( 'Table' ), 'Table'[DDMMYY] ), 'Table'[Value2] ) )

Value Y = SUMX ( VALUES ( 'Table'[Name] ), MAXX ( TOPN ( 1, TOPN ( 1, CALCULATETABLE ( 'Table' ), 'Table'[Value1] ), 'Table'[DDMMYY], ASC ), 'Table'[Value2] ) )

Re: Return Value by max date where value is not equal to value of max date by dimension.

Anonymous — Mon, 24 Jul 2023 14:20:23 GMT

Value1 is right however Value2 can't be calculated by top n I think.

Max date by category is 1.6.2023
and the "Value X" for Category a is 6 and category b is 4 colored in orange
now I would like to find the Value Y based on Max date by category and
the value should not equal to Previous Value "Value X"

Re: Return Value by max date where value is not equal to value of max date by dimension.

ArpitVijay — Mon, 24 Jul 2023 15:35:27 GMT

tamerj1

Value1 is right however Value2 can't be calculated by top n I think.

Re: Return Value by max date where value is not equal to value of max date by dimension.

ArpitVijay — Mon, 24 Jul 2023 17:26:55 GMT

Tahreem24

May you please check I found your solution to be useful here.

https://community.fabric.microsoft.com/t5/Desktop/Get-Value-associated-with-Max-Date-Per-Group/m-p/2460226#M878054

Re: Return Value by max date where value is not equal to value of max date by dimension.

tamerj1 — Tue, 25 Jul 2023 07:21:38 GMT

Anonymous