Compare Rows

[Anonymous]

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,

[amitchandak · Accepted solution]

@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) 

[amitchandak · Accepted solution]

@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) 

[Anonymous · Accepted solution]

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.

[Anonymous]

Instead of using DAX I just added the index in query and it worked.

Thanks