Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Top N table using dates from calendar table

Pls consider following tables and relationships:       I want to make a visual showing the Top 10 sales amount per salesrep (from table A) per date, with date using the calendar table so I ...
  • Anonymous's avatar
    Anonymous
    5 years ago

    I can get it to work without adding a column in power query and having to create a rule to transform the unreadable date in Table A to a date that is similar to Calendar[Date] by adding a column in DAX using:

    column: Table A Proper transaction Date = RELATED(Calendar[Date])

     It figures out the correct corresponding date from the calendar table into Table A, presumably through the Transaction ID relation it has with table B (the table that has the 'primary' relationship with the calendar table).

     

    Then adjust the RankX formula to following:

    Rank Sales per salesrep per day = RANKX(ALLSELECTED('Table A'[Name salesrep], 'Table A'[Table A Proper transaction Date]), CALCULATE(SUM('Table A'[Amount])))

     

    Is there a cleaner way to do this or did I manage to answer my own question?