Divide Expenses into months

[LeiteFel]

I have a a table of employees and their yearly salaries, all employees have a 1 year contract. I need to divide their salaries into the 12 months after they have started working (This date is on a column).

I need a bar chart that will have monthly expenses, summing all the salaries that will be payed each month.

How do I do it?

Example, lets say employee A has started in june 2023 and employee B on august 2023, the chart of monthly expenses need to look like this:

[DataNinja777 · Accepted solution]

Hi @LeiteFel ,

There are multiple ways to achive your required output and one of them is as follows:

I first created a dummy table like below which shows the yearly salary and the start date for 1 year contract employees.  

I then wrote a dax measure like below to split the yearly salary into monthly salary.  

Generated Table =
VAR ds =
    SUMMARIZE (
        'Salary table',
        'Salary table'[Employee name],
        'Salary table'[Start Date],
        'Salary table'[Yearly Salary]
    )
VAR gs =
    GENERATESERIES ( 1, 13 )
VAR result =
    GENERATE ( ds, VAR salary = [Yearly Salary] RETURN gs )
RETURN
    ADDCOLUMNS (
        DISTINCT ( result ),
        "eomonth", EOMONTH ( [Start Date], [Value] - 1 ),
        "beginning of month",
            EOMONTH ( [Start Date], [Value] - 2 ) + 1,
        "End Date", EDATE ( [Start Date], 12 ) - 1
    )

As the salary part required prorating for days and gets a bit complicated, I created a calculated column instead of combining in one table measure above.  

Monthly salary =
VAR PCT_Start_Month = ( [eomonth] - [Start Date] ) / ( [eomonth] - [beginning of month] )
VAR PCT_End_Month = ( [End Date] - [beginning of month] + 1 ) / ( [eomonth] - [beginning of month] )
RETURN
    (
        IF ( [Value] = 1, PCT_Start_Month, IF ( [Value] = 13, PCT_End_Month, 1 ) )
    ) * [Yearly Salary] / 12

The generated table from the above mesure and calculated column is shown below:

I then created a calculated calendar table and the employee name dimension table and created relationship with the fact tables:

The resultant visualiation of the numerical output is as shown below:

Your required output of stacked column chart is shown below.  

The point I need to mention regarding this solution is instead of using 12 to generateseries, we are using 13, because I thought most of the employees are not starting from 1st of the month, and the month 1 and month 13 monthly salaries need to be prorated for this reason. 

A quick accuracy check of the output using an employee example who joined on the 1st of the month confirms that this formula properly works to produce correct output in such case, too.

I attach an example pbix file below:

Best regards,

[LeiteFel]

Hi @DataNinja777 

This has completely solved my problem. Thank you for such a detailed response!