trouble with filters on two date ranges and date difference

[adam584]

I have 2 tables, 1 with client authorization and one with client staff relationships.  For a given date range I need to know how many days a client auth was valid AND how many days each staff had a relationship with them.  I then will multiply the number of days a staff had a relationship and the client had a valid auth with a number that is in the client auth table.

The two tables join on client ID.  I have created a date table.  I have successfully used the min/max with a measure on the date slicer, but if I try to use that in a formula against the client auth or staff relationship table I get the min and max value for the entire date table.

I have tried to use the same thing in a calculated column but have not been successful.

In short, I need to know –

Given a specified date range, how many days client had a valid auth and, how many of those days each staff who had a relationship had so that I can multiply it against a column in the auth table.

link to pbix https://wp.options.org/wp-content/uploads/2021/05/for-post.zip

[Anonymous · Accepted solution]

Hi @adam584 

Build a new date table and use max, min to get start and end date in slicer.

Do you want to filter clients by start day and end day in slicer ? Then calculate the daydiff between begin and end date to get auth days and relate days.

Date = CALENDARAUTO()

Measure = 
VAR _StartDay = MIN('Date'[Date])
VAR _EndDay = MAX('Date'[Date])
VAR _AuthDatediff = DATEDIFF(MAX(Client_auth[begin_date]),MAX(Client_auth[end_date]),DAY)
VAR _RelatDateDiff = DATEDIFF(MAX(Client_Staff[Relation_begin_date]),MAX(Client_Staff[Relation_end_date]),DAY)
VAR _Product = _AuthDatediff*_RelatDateDiff
VAR _Result = IF(AND(AND(MAX(Client_auth[begin_date])>=_StartDay,MAX(Client_auth[end_date])<=_EndDay),AND(MAX(Client_Staff[Relation_begin_date])>=_StartDay,MAX(Client_Staff[Relation_end_date])<=_EndDay)),_AuthDatediff*_RelatDateDiff,BLANK())
RETURN
_Result

Result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[adam584]

a zip of the pbix is here

https://wp.options.org/wp-content/uploads/2021/05/for-post.zip

it has reduced info but the main components are there