How to Report on an End date that falls within 2 Date Columns from another Table??

[amarris]

I am trying to solve a bit of a conundrum I have come up against.  I appreciate any time/support/tips you all may have in order to solve this. 

Issue: I am trying to find the count of policies that are active and in force during a user selected date, allowing me to report on any date and see the policies that are effective during that date.  In sql, I can achieve this by using the below query: 

"Declare @BeginningPeriod as date = '2022-01-01'
Declare @EndPeriod as date = '2022-03-31'

select count(AmsPolicyId) as TotalPL

from ProspectPolicy as PP

left join dw.OriginalPolicyTransactionTypes as OPTT on OPTT.Id = PP.Id

where
@EndPeriod between PP.AmsPolicyEffectiveDate and

case when PP.AMSPolicyStatus = 'C' then
case when PP.AmsPolicyEffectiveDate = OPTT.NewestTransactionEffectiveDate then PP.AmsPolicyExpirationDate
else OPTT.NewestTransactionEffectiveDate end
else PP.AmsPolicyExpirationDate end

and PP.AmsPolicySubType = 'Policy' and (PP.TypeOfBusiness = '0' or PP.TypeOfBusiness = '1')
and PP.DeletedBy is nullE
and PP.AmsPolicyId is not null"

So essentially, I am trying to allow my users to declare an "endperiod" date and get power bi to evaluate if that end period falls between the date parameters dictated in my query and return the total number of policies in force at that time.  

Steps I have taken so Far: 

1) I imported the table "Prospect Policy" which contains my AMSPolicyEffectivedate and the AMSPolicyExpirationDate.  I also imported the table "dw.OriginalPolicyTransactionTypes" which contains my column: "OPTT.NewestTransactionEffectiveDate".  

2) I related the table on ProspectPolicyID and OriginalPolicyTransactionTypesID in my data model/schema (as they are corresponding fields in the db).  

3) I created a column in my Prospect Policy Table to bring in the OPTT.NewestTransactionEffectiveDate into my Prospect Policy Table: 

OPTT.Newesttransactioneffectivedate = Lookupvalue('dw OriginalPolicyTransactionTypes'[NewestTransactionEffectiveDate],'dw OriginalPolicyTransactionTypes'[Id],ProspectPolicy[Id])

4) I then created another column to find the correct date that should be returned based on the criteria in my SQL query:

AMSPolicyStatus Return Date Element 2 = IF(ProspectPolicy[AmsPolicyStatus]= "C" && ProspectPolicy[AmsPolicyEffectiveDate] <> ProspectPolicy[OPTT.Newesttransactioneffectivedate],ProspectPolicy[OPTT.Newesttransactioneffectivedate],ProspectPolicy[AmsPolicyExpirationDate])

I then created a measure that filters the rest of the criteria for what i am looking for to return the total policies in force: 

Policies in Force - Personal Lines (Total) = Calculate(Count(ProspectPolicy[AmsPolicyId]),ProspectPolicy[AmsPolicySubType] = "Policy",ProspectPolicy[TypeOfBusiness] =0 || ProspectPolicy[TypeOfBusiness]=1,ProspectPolicy[DeletedBy]=BLANK(),ProspectPolicy[AmsPolicyId]<>BLANK())

My remaining issue is this: How do create a filter for the "Endperiod" date that will only return the count of my values in my measure that have the user selected "endperiod" date between the "Amspolicyeffectivedate" and the date from the calculated column I created: "AMSPolicyStatus Return Date Element 2"? 

[amarris]

Additional Question with this one - Now that I have the policy count, I want to try to get a distinct count on the propect ID associated with the policies. 

Essentially, in the same table there is a prospectid assigned to each policy.  There can be multiple policy records with the same ProspectID so I want to filter the result down to just the distinct ProspectId's. 

Below is the dax measure I created but currently it is giving me the full count of policies, not grouped by prospects: 

Customers Insured - Personal Lines (Total) = CALCULATE(COUNTx(FILTER(PP_PersonalLines,PP_PersonalLines[AmsPolicyEffectiveDate]<=max('Date'[Date]) && (ISBLANK(PP_PersonalLines[AMSPolicyStatus Return Date Element 2]) || PP_PersonalLines[AMSPolicyStatus Return Date Element 2]>max('Date'[Date]))),(PP_PersonalLines[ProspectID])),CROSSFILTER(PP_PersonalLines[AmsPolicyEffectiveDate],'Date'[Date],None))

[amitchandak · Accepted solution]

@amarris , Using an independent date table for date selection

measure =
var _max = maxx(allselected(Date1),Date1[Date])
return
calculate(
Count(ProspectPolicy[ProspectPolicyID]), Filter(ProspectPolicy, ProspectPolicy[OPTT.Newesttransactioneffectivedate] <=_max &&
ProspectPolicy[AmsPolicyExpirationDate] >= _max))

also check - Active employee code here

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[amarris]

Thank you @amitchandak - Using your Active Employee code tutorial was incredibly helpful and assisted me in getting it done. 

Great work and thank you for the assitance!!