Multiple Date filter/ relationship

[timazarj]

Hi,

My model is simple, a date table for filtering monthly reports, PL Service which is recording the services, Inserted date, Effective Date, & Closing Date, AM Table for service person name and ID. 

There will be two different measures MidTermCancellations based on the inserted date and Cancellation based on the effective date:

Cancellation = CALCULATE( DISTINCTCOUNT('PL Service'[ClientEntity]),

'PL Service'[ActivityCode]="CPOL",

USERELATIONSHIP('PL Service'[EffectiveDate],'Date'[Date]))

MidTermCancellations = CALCULATE(DISTINCTCOUNT('PL Service'[ClientEntity]),

'PL Service'[ActivityCode]="CPOL",

, USERELATIONSHIP('PL Service'[InsertedDate], 'Date'[Date]))

But I get blank for the MidTerm Cancellation which should be 146:

The date filter is from the date table.  Please guide me.

[amitchandak · Accepted solution]

@timazarj , can you check InsertedDate has a timestamp. If so join might fail

create a date like 

Date = [InsertedDate ].date
or
Date = date(year([InsertedDate ]),month([InsertedDate ]),day([InsertedDate ]))

[amitchandak · Accepted solution]

@timazarj , can you check InsertedDate has a timestamp. If so join might fail

create a date like 

Date = [InsertedDate ].date
or
Date = date(year([InsertedDate ]),month([InsertedDate ]),day([InsertedDate ]))

[timazarj]

Thank you, it worked.

[Anonymous]

Hello @timazarj 
Can you please share your pbix file or sample data after removing the sensitive data?

[timazarj]

Unfortunately no.

[Anonymous · Accepted solution]

Hello @timazarj 
You can try few things.
1. Check the column format and make it date.
2. Mark the date table as a date table.
3. Check the min and max of both the Inserted Date and Effective Date and create the date table accordingly using min and max values.

4. Create an active relationship with the inserted date and modify the measure accordingly and check.