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

[ArpitVijay]

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

[tamerj1]

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]
    )
)

[ArpitVijay]

@tamerj1 

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"

[tamerj1 · Accepted solution]

@wiz_lamp 

[wiz_lamp]

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"

[ArpitVijay]

@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/2...

[Manoj_Nair]

@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