Identify NEW clients to a service - from SQL to DAX

[Gaven]

Trying to identify clients who have not engaged with a service before (but may have already engaged with another service so are already in the database). Have previously managed this with SQL but not sure how to convert to DAX. 

The SQL looks lit this:

set @StartDate = '20190401'
set @EndDate = '20200331'
set @service = 'SWRK'

select * from
(
--Total
select a.actiontypeid Service, 'Total Clients Seen' ColumnName, count(distinct a.personid) as [Count],'' as [ ]
from resadm.action A
inner join resadm.Person P on P.PersonID = A.PersonID
where a.dateofaction >= @StartDate
and a.dateofaction <= @EndDate
--and a.status='A'
--and P.RecClassID = 'CCM'
and a.actiontypeid in ('AODAD','AODYTH','WWO','HDX','CMH','TH1')
and a.actiontypeid = @service
group by a.actiontypeid

union

--Total New
select a.actiontypeid Service, 'Total New Clients' ColumnName, count(distinct a.personid) as [Count],'' as [ ]
from resadm.action A
inner join resadm.Person P on P.PersonID = A.PersonID
left join resadm.action A2 on A2.PersonID = A.PersonID and A2.DateOfAction < @StartDate
where a.dateofaction >= @StartDate
and a.dateofaction <= @EndDate
--and a.status='A'
--and P.RecClassID = 'CCM'
and A2.ActionID is null
and a.actiontypeid in ('AODAD','AODYTH','WWO','HDX','CMH','TH1')
and a.actiontypeid = @service
group by a.actiontypeid

[amitchandak]

@Gaven , refer if this can help

new vs repeat
https://community.powerbi.com/t5/Desktop/Churn-Rate-lost-Customer/m-p/1173754#M529196
https://blog.enterprisedna.co/new-vs-existing-customers-advanced-analytics-w-dax/
https://www.sqlbi.com/articles/computing-new-customers-in-dax/

[Greg_Deckler]

@Gaven - Try this: https://community.powerbi.com/t5/Quick-Measures-Gallery/New-and-Returning-Customers/m-p/168297#M13

I also cover New, Returning and Lapsed customers in my book, DAX Cookbook. 

Also, since you are a New Member:

Please always first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.