Forum Discussion
Compare Rows
Hi all,
I have the a table called "Plan" which looks like this:
| ResourceName | Date | Task | Total Capacity Per Month |
| A | 1 January, 2022 | A | 147 |
| A | 1 February, 2022 | B | 147 |
| B | 1 March, 2022 | D | 161 |
| B | 1 March, 2022 | A | 161 |
| B | 1 March, 2022 | C | 161 |
| C | 1 April, 2022 | A | 140 |
| C | 1 April, 2022 | B | 140 |
| C | 1 May, 2022 | C | 154 |
| D | 1 May, 2022 | D | 154 |
| E | 1 May, 2022 | B | 154 |
I am trying to add a column to it for unique capacity per month for each resource. So I am looking to have something like this:
| ResourceName | Date | Task | Total Capacity Per Month | Unique Capacity Per Month |
| A | 1 January, 2022 | A | 147 | 147 |
| A | 1 February, 2022 | B | 147 | 147 |
| B | 1 March, 2022 | D | 161 | 161 |
| B | 1 March, 2022 | A | 161 | 0 |
| B | 1 March, 2022 | C | 161 | 0 |
| C | 1 April, 2022 | A | 140 | 140 |
| C | 1 April, 2022 | B | 140 | 0 |
| C | 1 May, 2022 | C | 154 | 154 |
| D | 1 May, 2022 | D | 154 | 154 |
| E | 1 May, 2022 | B | 154 | 154 |
Thank you in advance for your help,
Anonymous , You need an index column for that. Add that in power query
new column in dax
if( [Index] = minx(filter(Table, [resourcename] = earlier([resourcename]) && [Date] = earlier([date]) ), [Index] ) ,[Total capacity per monnth], 0)
Thanks for this, but I am having a problem getting the index. I tried:
Index 1 = RANKX(Plan,Plan[Date],,ASC,Dense)Grand Index = RANKX (Plan,Plan[Index 1]+ RANKX ( Plan, Plan[ResourceName],, ASC, DENSE )* MAX ( Plan[Index 1] ),,ASC,DENSE)but because I dont have unique names or dates I can get a unique number for each row.
3 Replies
Anonymous , You need an index column for that. Add that in power query
new column in dax
if( [Index] = minx(filter(Table, [resourcename] = earlier([resourcename]) && [Date] = earlier([date]) ), [Index] ) ,[Total capacity per monnth], 0)
Thanks for this, but I am having a problem getting the index. I tried:
Index 1 = RANKX(Plan,Plan[Date],,ASC,Dense)Grand Index = RANKX (Plan,Plan[Index 1]+ RANKX ( Plan, Plan[ResourceName],, ASC, DENSE )* MAX ( Plan[Index 1] ),,ASC,DENSE)but because I dont have unique names or dates I can get a unique number for each row.