Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Required DAX Formula

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's avatar
    Anonymous
    4 years ago

    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.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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.