Measure that counts on a date column other than the date column that is used in a slicer.

[Isidro]

Hello,

I have the following table:

Nº	START DATE	END DATE
1	01/04/2010	08/06/2014
2	08/06/2010	10/04/2015
3	06/06/2012	08/04/2015
4	01/10/2012	06/08/2016
5	04/03/2013	10/10/2016
6	06/06/2013	14/11/2017
7	14/04/2014	15/12/2017
8	20/11/2014	10/06/2017
9	15/11/2015
10	16/10/2016
11	14/02/2017
12	08/08/2017
13	20/02/2018
14	04/05/2018

This table is related to a calendar table through the start date. 

I have a slicer created with the year of the calendar table. What I want is to know how I can count the data in the end date column in each year.

For example, if you filter in 2010, the number to count of the end date column is 0 and if you filter in 2015, the number to count of the end date column is 2.

Thank you very much and best regards.

[Greg_Deckler]

Measure = COUNTROWS(VALUES('Table'[END DATE]))

?

[Isidro]

Hello @Greg_Deckler,

I have tried the measurement but the result that it gives me is erroneous.

Thank you very much for your help and best regards.

[Anonymous]

Hi @Isidro,

Have you tried: Measure = COUNTA(Table1[END DATE]) ?

This gives me the answers you are looking for. Hope this helps.

Edit: note that I have related the Date table with the END DATE

[Isidro · Accepted solution]

Hello @Anonymous and @Greg_Deckler,

I found the solution with the USERELATIONSHIP function. As you said, the end date column is related to the calendar table, with the inactive relation. Then I created the following formula:

MEASURE = CALCULATE (COUNT(TABLE1(END_DATE)); USERELATIONSHIP(TABLE1(END_DATE);CALENDARTABLE(DATE))

The USERELATIONSHIP function activates the relation for the calculation.

Thank you very much and best regards.

[Isidro]

Hello @Anonymous,

It works if I change the relationship of the calendar table with the column, but I also need to do a count with the start date column. I need to be able to count with the existing relationship.

Thank you very much for your help and best regards.