Forum Discussion
Top N table using dates from calendar table
- Anonymous5 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?
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?