Required DAX Formula

[Anonymous]

Dear Sir,

My query is to defined retailer class ( updated Retailer Class in trail mail) basis on the latest month of retailer transaction in power BI.

Please help to resolve this issue.

Retailer UID	Retailer Class	Month	Value	Updated Retailer Class	Remarks
#	B	01-06-2022	0.04771	E	Based on latest month(Ex. 01-07-2022)
#	E	01-05-2022	0.03347	E	Based on latest month(Ex. 01-07-2022)
#	E	01-06-2022	0.01668	E	Based on latest month(Ex. 01-07-2022)
#	E	01-07-2022	0.00902	E	Based on latest month(Ex. 01-07-2022)
AP-0000001	C	01-05-2022	0.00229	D	Based on latest month(Ex. 01-08-2022)
AP-0000001	D	01-06-2022	0.00229	D	Based on latest month(Ex. 01-08-2022)
AP-0000001	D	01-08-2022	0.0048	D	Based on latest month(Ex. 01-08-2022)
AP-0000003	C	01-05-2022	0.00575	D
AP-0000003	D	01-06-2022	0.00574	D
AP-0000004	C	01-05-2022	0.0062	B
AP-0000004	B	01-06-2022	0.0056	B
AP-0000005	D	01-05-2022	0.00229	A
AP-0000005	D	01-06-2022	0.00699	A
AP-0000005	A	01-07-2022	0.00581	A
AP-0000006	C	01-05-2022	0.01172	D
AP-0000006	D	01-06-2022	0.00319	D

[Anonymous]

thanks

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try this code to create a calculated column.

Updated Retailer Class Remarks =
VAR _LAST_MONTH =
    CALCULATE (
        MAX ( 'Table'[Month] ),
        ALLEXCEPT ( 'Table', 'Table'[Retailer UID] )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Retailer Class] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Retailer UID] ),
            'Table'[Month] = _LAST_MONTH
        )
    )

Result is as below.

Best Regards,
Rico Zhou

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

[Anonymous]

hi,

when we use this in power pivot data modle it's showing error

how can we use in power pivot data model

Error is :

The function MAX takes an argument that evaluates to numbers or dates and cannot work with values of type String.