Dynamically calculate employee count in column using previous month data

Hi Anonymous ,
Depending on the information you provided, you can follow these steps below:
1.Merge Queries in Power Query.

2.Add new columns.

Month = MONTH('Monthly Data for New Joinee'[Month Year])

Expected Output =
VAR _support = ( 1 - ( 'Monthly Data for New Joinee'[Annual File.Attrition Rate] / 12 ) )
VAR _1 = 'Monthly Data for New Joinee'[Annual File.Employee (Base Count)] * _support + 'Monthly Data for New Joinee'[# Joinee]
VAR _2 = _1 * _support + 'Monthly Data for New Joinee'[# Joinee]
VAR _3 = _2 * _support + 'Monthly Data for New Joinee'[# Joinee]
VAR _4 = _3 * _support + 'Monthly Data for New Joinee'[# Joinee]
VAR _5 = _4 * _support + 'Monthly Data for New Joinee'[# Joinee]
VAR _6 = _5 * _support + 'Monthly Data for New Joinee'[# Joinee]
VAR _7 = _6 * _support + 'Monthly Data for New Joinee'[# Joinee]
VAR _8 = _7 * _support + 'Monthly Data for New Joinee'[# Joinee]
VAR _9 = _8 * _support + 'Monthly Data for New Joinee'[# Joinee]
VAR _10 = _9 * _support + 'Monthly Data for New Joinee'[# Joinee]
VAR _11 = _10 * _support + 'Monthly Data for New Joinee'[# Joinee]
VAR _12 = _11 * _support + 'Monthly Data for New Joinee'[# Joinee]
RETURN
    IF (
        'Monthly Data for New Joinee'[Month] = 1,
        _1,
        IF (
            'Monthly Data for New Joinee'[Month] = 2,
            _2,
            IF (
                'Monthly Data for New Joinee'[Month] = 3,
                _3,
                IF (
                    'Monthly Data for New Joinee'[Month] = 4,
                    _4,
                    IF (
                        'Monthly Data for New Joinee'[Month] = 5,
                        _5,
                        IF (
                            'Monthly Data for New Joinee'[Month] = 6,
                            _6,
                            IF (
                                'Monthly Data for New Joinee'[Month] = 7,
                                _7,
                                IF (
                                    'Monthly Data for New Joinee'[Month] = 8,
                                    _8,
                                    IF (
                                        'Monthly Data for New Joinee'[Month] = 9,
                                        _9,
                                        IF (
                                            'Monthly Data for New Joinee'[Month] = 10,
                                            _10,
                                            IF ( 'Monthly Data for New Joinee'[Month] = 11, _11, _12 )
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )

 Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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