Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rankx with Dates

  Hi all,    I have the table like that and want to create a report with a RankX     Customer Material Order Date RankX A 123 1.1.2019 1 A 123 1.1.2019 2 A 123 3.3.2019 ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

     

    Create a Calculated Column

     

    RANK Column = RANKX(FILTER('Table','Table'[Customer] = EARLIER('Table'[Customer]) && 'Table'[Material]= EARLIER('Table'[Material])),'Table'[Order_date],,ASC)
     
     
     

    Regards,
    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

v-xicai's avatar
v-xicai
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

You may create rank using column or measure like DAX below.

 

Column: Rankx = RANKX(FILTER(Table, Table[Customer]=EARLIER(Table[Customer])&&Table[Material]=EARLIER(Table[Material])),Table[Order Date],,ASC ,Skip)


Measure: Rankx = RANKX(FILTER(Table, Table[Customer]=MAX(Table[Customer])&&Table[Material]=MAX(Table[Material])), MAX(Table[Order Date]),,ASC ,Skip)

 

Best Regards,

Amy 

 

Community Support Team _ Amy

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
    6 years ago

    Hi all, 

     

    both solutions are showing different numbers from what I expected.

    Let me rephrase my request.

     

    I have this table:

     

    Format:

    Soldto:      123/ABC

    Material:    123/ABC

    Order date:  Whole number (I displayed the dates in the table below in date format)

     

    CustomerMaterialOrder Date
    A1231.1.2019
    A1232.2.2019
    A1233.3.2019
    A14510.1.2019
    A14512.1.2019
    A1867.1.2019
    B12320.1.2019
    B1239.1.2019

     

    I want to create a report, which shows me the following.

    A ranking (does need to be with formula rank) like this:

     

    I want to see on (Customer & Product) level a ranking of the order dates. 

    For example

    Customer A with Product 123 for order date: 1.1.2019 = 1

    Customer A with Product 123 for order date: 2.2.2019 = 2

    Customer A with Product 123 for order date: 3.3.2019 = 3

    Customer A with Product 145 for order date: 10.1.2019 = 1

    Customer A with Product 145 for order date: 12.1.2019 = 2

    ...

     

    Do you understand what I mean?

     

    BR

    Lanko

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

       

      You can use the below meaures.

       

      Ranking =

      var _a = MAX(Table1[Customer])
      var _b = MAX(Table1[Material])

      return

      RANKX(FILTER(ALL(Table1[Material],Table1[Customer],Table1[Order Date]), Table1[Customer] = _a && Table1[Material] = _b), CALCULATE( MAX(Table1[Order Date])),,ASC ,Skip)



       
       
       
      Thanks and Regards,
      Harsh Nathani
      Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thanks a lot Anonymous ,

         

        I added measureB sumx (Format:numbers) to the report and now I want to add another MeasureC to the report.

         

        MeasureC

        Shall be the variance (var.p) for every "Customer and Material" combination like the rankx.

        Unfortunately var.p needs a column and don't work with measureB.

         

        Can somebody help me out here? 

         

         

        CustomerMaterialOrder Date

        MeasureA

        Rankx

        (based on your filter formula)

        MeasureB

        Sumx

        (based on your filter formula)

        MeasureC

        Var.P("on MeasureA")

        based on 

        A1231.1.2019   
        A12310.1.2019   
        B......   
        B     
        C     
        C     

         

        Thanks

        lanko