FIND LATEST CLASSIFICATION OF RETAILER BASED ON LATEST DATE

[patroriki]

Dear All

Request you to help in DAX to find latest retailer UID classification based on its latest date.

Retailer UID	Retailer Class	Month	Updated Retailer Class	Remarks
AP-0000001	C	01-05-2022	D	Based on latest month(Ex. 01-08-2022)
AP-0000001	D	01-06-2022	D	Based on latest month(Ex. 01-08-2022)
AP-0000001	D	01-08-2022	D	Based on latest month(Ex. 01-08-2022)
AP-0000003	C	01-05-2022	D
AP-0000003	D	01-06-2022	D
AP-0000004	C	01-05-2022	B
AP-0000004	B	01-06-2022	B
AP-0000005	D	01-05-2022	A
AP-0000005	D	01-06-2022	A
AP-0000005	A	01-07-2022	A
AP-0000006	C	01-05-2022	D
AP-0000006	D	01-06-2022	D

[v-zhangti · Accepted solution]

Hi, @patroriki 

You can try the following methods.

Measure:

Latest date UID = 
CALCULATE (
    MAX ( 'Table'[Retailer UID] ),
    FILTER (
        ALL ( 'Table' ),
        [Month] = CALCULATE ( MAX ( 'Table'[Month] ), ALL ( 'Table' ) )
    )
)

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.

[Ashish_Mathur]

Hi,

Try this calculated column formula

=coalesce(

LOOKUPVALUE(Data[Retailer Class],Data[Month],CALCULATE(MAX(Data[Month]),FILTER(Data,Data[Retailer UID]=EARLIER(Data[Retailer UID])&&Data[Month]>EARLIER(Data[Month]))),Data[Retailer UID],Data[Retailer UID]),Data[Retailer Class])

Hope this helps.

[amitchandak]

@patroriki , Try a new column like

New column =
var _max = maxx(filter(Table, [Retailer UID] = earlier([Retailer UID]) ), [Month])
return
maxx(filter(Table, [Retailer UID] = earlier([Retailer UID]) && [Month] =_max),[Updated Retailer Class])

or

New column =
var _max = maxx(filter(Table, [Retailer UID] = earlier([Retailer UID]) ), [Month])
return
maxx(filter(Table, [Retailer UID] = earlier([Retailer UID]) && [Month] =_max),[Retailer Class])