Count how many in a point in time

[android1]

Hi,

I have a table 'ClientsList' with columns 

ClientID	ClientRef	StartDate	EndDate	ActiveYN
Adna yaxyx	22936	19/04/2018		Y
Agnzs Hznnzsszy	17993	31/01/2018	24/04/2018 00:00	N
Axdan Cullzn	22807	27/02/2018		Y
Axdan O'Shza	21791	22/02/2018		Y

I need the number of ClienID who were Active on 1/1/2018.

I have come up with this Measure but it's returning zero results. 

Tot clients start of 2018 = CALCULATE(DISTINCTCOUNT(ClientsList[ClientRef]),FILTER(ClientsList,ClientsList[EndDate] >= DATE(2018,1,1)), ClientsList[StartDate] <= DATE(2017,12,31)) + CALCULATE(DISTINCTCOUNT(ClientsList[ClientRef]),FILTER(ClientsList,ClientsList[EndDate] = BLANK()),ClientsList[StartDate] <= DATE(2017,12,31)) - CALCULATE(DISTINCTCOUNT(ClientsList[ClientRef]),FILTER(ClientsList,ClientsList[EndDate] = BLANK()),ClientsList[StartDate] <= DATE(2018,1,1),ClientsList[ActiveYN] = "N") + 0

See attached sample data.

https://www.dropbox.com/s/r3mdt7wvg74z1m1/data.csv?dl=0

[Anonymous]

PLEASE, use this http://www.daxformatter.com/raw/

And try this:

Tot clients start of 2018 = 
CALCULATE (
    DISTINCTCOUNT ( ClientsList[ClientRef] );
    ClientsList[EndDate] >= DATE ( 2018; 1; 1 ) ; ClientsList[StartDate] <= DATE ( 2017; 12; 31 ))
    + CALCULATE (
        DISTINCTCOUNT ( ClientsList[ClientRef] );
        ClientsList[EndDate] = BLANK () ; ClientsList[StartDate] <= DATE ( 2017; 12; 31 ))
    - CALCULATE (
        DISTINCTCOUNT ( ClientsList[ClientRef] );
        ClientsList[EndDate] = BLANK () ; ClientsList[StartDate] <= DATE ( 2018; 1; 1 ) ; ClientsList[ActiveYN] = "N")
    + 0

[Greg_Deckler]

Perhaps take a look at these two Quick Measures that I created to deal with date range type of issues:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364