No results

Now I am getting results. Unfortunately it is not wat I am looking for.

I want to calculate the number of starting and stopping members. 

The columns 'year+1' and 'year-1' are calculated with column 'year'.

The columns 'also member following year' and 'was member last year' are the columns I am looking for a DAX-formula.

In Excel it would be for 'also member following year': countifs(B:B;B2;A:A;C2)

And for 'was member last year': countifs(B:B;B2;A:A;E2)

If there are other options I am interested as well.

Thanks Ashish_Mathur and krishnakanth240 both your solutions worked out for me!

Count =

VAR vB = SELECTEDVALUE('Table'[ID])

VAR vD = SELECTEDVALUE('Table'[Year+1])

RETURN

IF(

 NOT(ISBLANK(vB)) && NOT(ISBLANK(vD)),

 COUNTROWS(

 FILTER(

 ALLEXCEPT('Table', 'Table'[ID], 'Table'[Year+1]),

 'Table'[ID] = vB && 'Table'[Year] = vD

 )

 ),

 BLANK()

)

It's a little hard to test without the data but can you try this?

Thanks. Unfortunately I am getting the message about a circulair dependency detected: Table[Count]

3s 

maybe you can try this to create a calculated column.

Count =
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[ID] = EARLIER('Table'[ID])
&& 'Table'[Year] = EARLIER('Table'[Year])
)
)

if this does not work, could you pls provide some sample data and expected output?

could you pls provide some sample data and expected output?

Hi 3s 

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.