SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2017 (RTM-CU30) (KB5013756) - 14.0.3451.2 (X64) 
	Jun 22 2022 18:20:15 
	Copyright (C) 2017 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE ClientTable (
    ClientId INT,
    SupervisorId INT,
    ManagerId INT,
    [Date] DATE,
    EXTRA CHAR(1)
);

CREATE TABLE ClientValues (
    ClientId INT,
    SupervisorId INT,
    ManagerId INT,
    ValueDate DATE,
    Value DECIMAL(5,2),
    EXTRA CHAR(1)
)
INSERT INTO ClientTable
VALUES
( 1,1,1, '2021-01-31', ''),
( 2,1,1, '2021-02-28', ''),
( 3,1,1, '2021-02-28', ''),
( 4,2,1, '2021-03-31', ''),
( 5,2,1, '2021-03-31', ''),
( 6,3,2, '2021-01-31', ''),
( 7,3,2, '2021-03-31', '')

INSERT INTO ClientValues
VALUES
( 1,1,1, '2021-01-31', 100.0, ''),
( 1,1,1, '2021-02-28', 90.0, ''),
( 1,1,1, '2021-03-31', 110.0, ''),
( 2,1,1, '2021-02-28', 50.0, '' ),
( 2,1,1, '2021-03-31', 75.0, '' ),
( 3,1,1, '2021-02-28', 100.0, ''),
( 3,1,1, '2021-03-31', 101.0, ''),
( 4,2,1, '2021-03-31', 90.0, ''),
( 5,2,1, '2021-01-31', 70.0, ''),

( 6,3,2, '2021-01-31', 10.0, ''),
( 6,3,2, '2021-02-28', 15.0, ''),
( 6,3,2, '2021-03-31', 30.0, ''),
( 7,3,2, '2021-03-31', 100.0, '')
20 rows affected

WITH aggregated_data AS (
    SELECT
        EOMONTH(cv.[ValueDate]) AS EoMDate,
        cv.ManagerId,
        (
            SELECT COUNT(DISTINCT ct.SupervisorId)
            FROM ClientTable ct
            WHERE EOMONTH(ct.[Date])<=EOMONTH(cv.[ValueDate]) AND ct.ManagerId = cv.ManagerId
        )  as Supervisors,
        (
            SELECT COUNT(DISTINCT ct.ClientId)
            FROM ClientTable ct
            WHERE EOMONTH(ct.[Date])<=EOMONTH(cv.[ValueDate]) AND ct.ManagerId = cv.ManagerId
        ) as Clients,
        SUM(cv.Value) as Value
    FROM
        ClientValues cv
    GROUP BY
        EOMONTH(cv.[ValueDate]), cv.ManagerId
)
SELECT
    EoMDate, 
    ManagerId,
    Supervisors,
    Clients,
    Value,
    Value - LAG(Value) OVER (PARTITION BY ManagerId ORDER BY EoMDate) as ChangeValue,
    CAST(100*(Value - LAG(Value) OVER (PARTITION BY ManagerId ORDER BY EoMDate))/LAG(Value) OVER (PARTITION BY ManagerId ORDER BY EoMDate) AS DECIMAL(5,2)) as ChangePerc
FROM
    aggregated_data
ORDER BY
    EoMDate,ManagerId


EoMDate
ManagerId
Supervisors
Clients
Value
ChangeValue
ChangePerc


2021-01-31
1
1
1
170.00
null
null

2021-01-31
2
1
1
10.00
null
null

2021-02-28
1
1
3
240.00
70.00
41.18

2021-02-28
2
1
1
15.00
5.00
50.00

2021-03-31
1
2
5
376.00
136.00
56.67

2021-03-31
2
1
2
130.00
115.00
766.67


SELECT
    EoMDate, 
    ManagerId,
    Supervisors,
    Clients,
    Value,
    Value - LAG(Value) OVER (PARTITION BY ManagerId ORDER BY EoMDate) as ChangeValue,
    CAST(100*(Value - LAG(Value) OVER (PARTITION BY ManagerId ORDER BY EoMDate))/LAG(Value) OVER (PARTITION BY ManagerId ORDER BY EoMDate) AS DECIMAL(5,2)) as ChangePerc
FROM
    (
    SELECT
        EOMONTH(cv.[ValueDate]) AS EoMDate,
        cv.ManagerId,
        (
            SELECT COUNT(DISTINCT ct.SupervisorId)
            FROM ClientTable ct
            WHERE EOMONTH(ct.[Date])<=EOMONTH(cv.[ValueDate]) AND ct.ManagerId = cv.ManagerId
        )  as Supervisors,
        (
            SELECT COUNT(DISTINCT ct.ClientId)
            FROM ClientTable ct
            WHERE EOMONTH(ct.[Date])<=EOMONTH(cv.[ValueDate]) AND ct.ManagerId = cv.ManagerId
        ) as Clients,
        SUM(cv.Value) as Value
    FROM
        ClientValues cv
    GROUP BY
        EOMONTH(cv.[ValueDate]), cv.ManagerId
) t
ORDER BY
    EoMDate,ManagerId


EoMDate
ManagerId
Supervisors
Clients
Value
ChangeValue
ChangePerc


2021-01-31
1
1
1
170.00
null
null

2021-01-31
2
1
1
10.00
null
null

2021-02-28
1
1
3
240.00
70.00
41.18

2021-02-28
2
1
1
15.00
5.00
50.00

2021-03-31
1
2
5
376.00
136.00
56.67

2021-03-31
2
1
2
130.00
115.00
766.67
EoMDate	ManagerId	Supervisors	Clients	Value	ChangeValue	ChangePerc
2021-01-31	1	1	1	170.00	null	null
2021-01-31	2	1	1	10.00	null	null
2021-02-28	1	1	3	240.00	70.00	41.18
2021-02-28	2	1	1	15.00	5.00	50.00
2021-03-31	1	2	5	376.00	136.00	56.67
2021-03-31	2	1	2	130.00	115.00	766.67