Calacuted column for inactive clients for specific date range

[Anonymous]

I have a calculated table that shows the date for the last 12 months from as shown below

Last_12_Months_Table =
VAR MaxDate = MAX(Calendar_table[Date])
RETURN
FILTER (
    Calendar_table,
    Calendar_table[Date] >= DATE(YEAR(MaxDate) - 1, MONTH(MaxDate), DAY(MaxDate))
        && Calendar_table[Date] <= MaxDate
)
What i want is to add a calcutated column in the Last_12_Months_Table that calculate  the total inactive clients who did not submit any project in the last 12 months according to onther table data which is SQL 2 and the date column is SubmitDate and the client name column is Submittedby, also please not that the date range exist in the SubmitDate column is back to 2008
i want the measure to check only backword till 1/1/2018 not all the table dates avaliabe.

I tried this measure but still not showing correct results, here is the calcauted column measure for the inactive clients i am using
Inactive_Client_Count =
VAR StartDate = EOMONTH(Last_12_Months_Table[Date], -12) + 1
VAR EndDate = EOMONTH(Last_12_Months_Table[Date], 0)
RETURN
CALCULATE (
    DISTINCTCOUNT('SQL 2'[SubmittedBy]),
    FILTER (
        'SQL 2',
        'SQL 2'[SubmitDate] < StartDate &&
        NOT 'SQL 2'[SubmittedBy] IN (
            SELECTCOLUMNS (
                FILTER('SQL 2', 'SQL 2'[SubmitDate] >= StartDate && 'SQL 2'[SubmitDate] <= EndDate),
                "SubmittedBy", 'SQL 2'[SubmittedBy]
            )
        )
    )
)

also here is a screenshot to make it more clear
please help me make it correct and to calcualte the total inactive client for each month of the last 12 months exist in the Last_12_Months_Table

[Anonymous]

Hi @Anonymous 

Try the following measure

Inactive_Client_Count = 
VAR CurrentDate = MAX('Last_12_Months_Table'[Date]) // Or use TODAY() for a dynamic date
VAR StartDate = MAX(DATE(2018, 1, 1), EOMONTH(CurrentDate, -12) + 1)
VAR EndDate = CurrentDate
RETURN
CALCULATE(
    COUNTROWS(
        EXCEPT(
            VALUES('SQL 2'[SubmittedBy]), // Unique clients
            CALCULATETABLE(
                VALUES('SQL 2'[SubmittedBy]),
                'SQL 2'[SubmitDate] >= StartDate && 'SQL 2'[SubmitDate] <= EndDate
            )
        )
    ),
    ALL('SQL 2') // Remove any filters on 'SQL 2'
)

If this measure doesn't help you solve your problem. please provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

Best Regards,

Jayleny

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

[Anonymous]

Hi @Anonymous 

Thank you for your prompt response. I truly appreciate it. Unfortunately, the dataset is quite extensive, making it time-consuming to share a sample. Therefore, I'll provide additional information to aid you in addressing this critical issue within my project.

Regarding your measure, it returned a fixed number, which was 10320. This aligns with the measure I initially provided and matches the figure I obtained for February 2024. However, neither of these figures ceased at 2018.

I attempted another measure, which seemingly reflects the correct results. However, it presents a challenge: it displays a single value for all twelve months preceding the current month available in the table. My aim is for this measure to be dynamic, providing a unique result for each of the last twelve months.

Here's the new measure I'm using:
Inactive_Client_Count =
VAR CurrentDate = MAX('Last_12_Months_Table'[Date])
VAR StartDate = EOMONTH(CurrentDate, -12) + 1
VAR EndDate = EOMONTH(Last_12_Months_Table[Date], 0)
RETURN
CALCULATE(
FILTER(
VALUES('SQL 2'[SubmittedBy]), // Unique clients
CALCULATE(
MAX('SQL 2'[SubmitDate]) <= StartDate
&& MAX('SQL 2'[SubmitDate]) >= DATE(2018,1,1)
)
),
ALL('SQL 2')
)

Please note, in the screenshot provided, the second "Inactive" column represents your measure, while the third column is the earlier measure I supplied.

Let me know if you need further clarification or assistance.