Calculating Number of Weeks in Date Slicer ignoring active Relationships

[TBSST]

Hi PowerBI Community, 

I am currently working on a DAX to calculate the number of weeks a client has been active based on a callender date slicer. I use 3 dates, a start date, and end date, and a survey date. The survey date has an active relationship with the date in the callender table as the survey date is the most used date filter for most of the reports. The start and end date has an inactive relationship with the callender date table. 

The DAX i am needing is one that identifies how many weeks a client has been active during a month (determined by the date slicer). With this i also need to calculate with the start and end date included aswell, so if a client starts halfway through a month, it only counts from the start date, not the start of the month sleected, and same logic for end dates. 

The main issue i am having with this calculation is that when i filter with 'TheDate', on an individual client levle, it is counting the min date as the first activity they had that month. So an example is if im looking at 1/1/2024 ro 31/1/2024, and the first survey recorded for a cient is on the 10/1/2024, the count of number of weeks start at the 10th, not the first for that client. Is there a way to ignore this relationship of survey date and the callender, or a way to only count the date slicer regardless of when a survey was completed. 

Any help with DAX on this would be greathly appreaciated. Been spending quite some time trying to troubleshoot this one!

Client ID	Start_Date	End_Date	SurveyID	Survey_Date
CL-1	1/01/2024	29/02/2024	SV-1	1/01/2024
CL-1	1/01/2024	29/02/2024	SV-2	8/01/2024
CL-1	1/01/2024	29/02/2024	SV-3	15/01/2024
CL-1	1/01/2024	29/02/2024	SV-4	25/01/2024
CL-1	1/01/2024	29/02/2024	SV-5	2/02/2024
CL-1	1/01/2024	29/02/2024	SV-6	10/02/2024
CL-1	1/01/2024	29/02/2024	SV-7	20/02/2024
CL-2	14/01/2024		SV-8	14/01/2024
CL-2	14/01/2024		SV-9	20/01/2024
CL-2	14/01/2024		SV-10	6/02/2024
CL-2	14/01/2024		SV-11	10/02/2024
CL-3	10/02/2024		SV-12	14/02/2024
CL-3	10/02/2024		SV-13	20/02/2024
CL-3	10/02/2024		SV-14	21/02/2024
CL-3	10/02/2024		SV-15	29/02/2024
CL-4	20/01/2024	20/02/2024	SV-16	24/01/2024
CL-4	20/01/2024	20/02/2024	SV-17	2/02/2024
CL-4	20/01/2024	20/02/2024	SV-18	10/02/2024
CL-4	20/01/2024	20/02/2024	SV-19	19/02/2024

Thank you. 

[BeaBF]

@TBSST ! Hi!

First of all disactivate the relationship between the Calendar Table and the Survey Date. Then try to add this measures:

SelectedPeriodStartDate =

CALCULATE(

    MIN(Calendar[Date]),

    ALLSELECTED('Calendar')

)

SelectedPeriodEndDate =

CALCULATE(

    MAX(Calendar[Date]),

    ALLSELECTED('Calendar')

)

ActiveWeeks =

VAR StartDate = SELECTEDVALUE('Table'[Start_Date])

VAR EndDate = SELECTEDVALUE('Table'[End_Date])

VAR PeriodStart = [SelectedPeriodStartDate]

VAR PeriodEnd = [SelectedPeriodEndDate]

VAR ActualStart =

    IF(

        StartDate < PeriodStart,

        PeriodStart,

        StartDate

    )

VAR ActualEnd =

    IF(

        ISBLANK(EndDate),

        PeriodEnd,

        IF(

            EndDate > PeriodEnd,

            PeriodEnd,

            EndDate

        )

    )

VAR DaysActive = DATEDIFF(ActualStart, ActualEnd, DAY)

VAR WeeksActive = DIVIDE(DaysActive, 7, 0)

RETURN

IF(DaysActive > 0,

    CEILING(WeeksActive, 1),

    0

)

BBF