cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
timazarj
Helper I
Helper I

Multiple Date filter/ relationship

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]))
 

timazarj_0-1623778922854.png

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

timazarj_1-1623779232412.png

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

2 ACCEPTED SOLUTIONS
amitchandak
Super User IV
Super User IV

@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 ]))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

TarunSharma
Responsive Resident
Responsive Resident

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.

View solution in original post

5 REPLIES 5
amitchandak
Super User IV
Super User IV

@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 ]))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Thank you, it worked.

TarunSharma
Responsive Resident
Responsive Resident

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

Unfortunately no.

TarunSharma
Responsive Resident
Responsive Resident

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.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates