auto increment column base on condition

[Anonymous]

Hi all,

I need to create a new column (Dax or PowerQuery) based on other column. This is my scenario

Country	date	Test	Indice
China	22/01/2020 0:00:00	548	2179
China	23/01/2020 0:00:00	643	2180
China	24/01/2020 0:00:00	920	2181
Spain	22/01/2020 0:00:00	0	9769
Spain	23/01/2020 0:00:00	0	9770
Spain	24/01/2020 0:00:00	0	9771
US	22/01/2020 0:00:00	1	11089
US	23/01/2020 0:00:00	1	11090
US	24/01/2020 0:00:00	2	11091

And this is what i would like to obatin:

Country	date	Test	Indice	WhatIWantIs
China	22/01/2020 0:00:00	548	2179	1
China	23/01/2020 0:00:00	643	2180	2
China	24/01/2020 0:00:00	920	2181	3
Spain	22/01/2020 0:00:00	0	9769	0
Spain	23/01/2020 0:00:00	0	9770	0
Spain	24/01/2020 0:00:00	1	9771	1
US	22/01/2020 0:00:00	1	11089	1
US	23/01/2020 0:00:00	1	11090	2
US	24/01/2020 0:00:00	2	11091	3

If column test is 0 , new column have to be 0 but if test is > 0 new column have to increment +1 also taking account the columns country and date to guarantee the integrity of data.

Thanks 

[amitchandak · Accepted solution]

@Anonymous 

Please try a new column like

What you want = COUNTX(FILTER(data,[Country]=EARLIER([Country]) && [date]<=EARLIER([date]) && [Test]>0),[date] )+0

File is attached after signature

[Anonymous]

Thanks , works perfect.