Check if ID previously existed outside of date range

[Arranafc19]

Hi,

Looking for some guidance on a measure I am working on , not sure where I am going wrong.

I am trying to look at a list of ids and get a distinct count of ids created within that time , however I need to be able to exclude any that have a previous record before my selected date range.

looking at the above example , between the date range of 01/08 and 02/08 , I have 4 cases that have a date added within that time , however two of these 13181 and 13190 have a record previously in the dataset outside the date range so I want to exclude these from the count as they arent new ids created in that range.

In the screenshot you can see my measure , however it seems to be affected by the slicer which is scewing the calculation.

My end goal would be something like this :

Calculate(distinctcount('IDS'[id]),Existed Previously = "No") 

so the count will only count cases that came in within the date range and it is the first time that they have existed in the whole dataset.

Anyone know where I am going wrong here ?

Thanks

[Arranafc19]

Hi @v-cgao-msft 

This works but as soon as I create a relationship between the date table and the ids table it breaks

I basically need this to filter to pull back all ids within a chosen date range , and then check if there has ever been a record previously and if so I need to exclude.

On the example file you gave I would be expecting to see on the 01/08 , 4 distinct ids and of those 4 , 2 are new because the other two had a record previously outside of the date range. Need to just see totals for this .

Also , can the check be for just those with a value ? If there the id is blank I dont want it excluded if there were blanks previous 

[v-cgao-msft]

Hi @Arranafc19 ,

Please try:

New IDs =
VAR MinDate =
    MIN ( 'Date'[Date] )
VAR MaxDate =
    MAX ( 'Date'[Date] )
RETURN
    IF (
        ISBLANK (
            CALCULATE (
                DISTINCTCOUNT ( 'IDS'[ID] ),
                FILTER (
                    'IDS',
                    'IDS'[Date Added] >= MinDate
                        && 'IDS'[Date Added] <= MaxDate
                        && CALCULATE ( COUNTROWS ( 'IDS' ), 'IDS'[Date Added] < MinDate ) = 0
                )
            )
        ),
        "No",
        "Yes"
    )

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum