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
Super User

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



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

View solution in original post

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
Super User

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



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Thank you, it worked.

TarunSharma
Skilled Sharer
Skilled Sharer

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

Unfortunately no.

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.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors