Dynamically calculate employee count in column using previous month data

[Anonymous]

Hi Team,

I need one help and will really appreciate your efforts to solve this!!

I need to build one report as  below , the ask is to calculate employee count for every month by using employee count from previous month, and add new joinee and subtract discontinued employee in current month. For initial start the employee base is coming from one Anual Input file for each country and new joinee data is coming from another file at monthly level. We have formula to calculate #employees discontinued each month

The formula to calculate Employee count every Month is - 

Sum(Employee base for country from Annual file)+ New Joinee from Monthly File - Discontinued Employee
**Discontinued Employee is calculated as (Employee base for that country from Annual file) * Attrition Rate for that country divided by 12 ( to get data for that 1 month)

For next month iteration, we need to use employee base from previous month not from Annual file

Expected Output for Country = Croatia where Employee base is 86 and Attrition Rate is 12% 

Example Calculation for Jan 2024 = 86(Annual File) + 7 (Monthly File) - (86*0.12/12) = 92 

For Feb = 92 (Data from Jan month) +7 (Monthly File) - (92*0.12/12)= 98

Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24
92	98	104	109	114	119	124	129	134	139	144	149

Sample Data for Reference

Annual File

Country	Attrition Rate	Employee (Base Count)
Croatia	12%	86
Austria	13%	100
Belgium	15%	75

Monthly Data for New Joinee

Country	Month Year	# Joinee
Croatia	Jan-24	7
Croatia	Feb-24	7
Croatia	Mar-24	7
Croatia	Apr-24	7
Croatia	May-24	7
Croatia	Jun-24	7
Croatia	Jul-24	7
Croatia	Aug-24	7
Croatia	Sep-24	7
Croatia	Oct-24	7
Croatia	Nov-24	7
Croatia	Dec-24	7
Austria	Jan-24	6
Austria	Feb-24	6
Austria	Mar-24	6
Austria	Apr-24	6
Austria	May-24	6
Austria	Jun-24	6
Austria	Jul-24	6
Austria	Aug-24	6
Austria	Sep-24	6
Austria	Oct-24	6
Austria	Nov-24	6
Austria	Dec-24	6
Belgium	Jan-24	6
Belgium	Feb-24	6
Belgium	Mar-24	6
Belgium	Apr-24	6
Belgium	May-24	6
Belgium	Jun-24	6
Belgium	Jul-24	6
Belgium	Aug-24	6
Belgium	Sep-24	6
Belgium	Oct-24	6
Belgium	Nov-24	6
Belgium	Dec-24	6

[Anonymous]

Thanks ! Really appreaciate it !

It does render the result correctly but it's kind of lengthy one when the month year is increased till Dec 2029. Is there any way to do it dynamically using some iteration/recursion in M query or may be by using window fuction "Offset"

[Anonymous]

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.