Sum salaries between a start date and end date/current time?

[Meraki]

Hi 🙂

I'm a newbie to power BI and I have a table with employee data: monthly salary, employee ID, employment start date and end date. I need to be able to calculate/SUM their salaries over any given year for a report (so - year to date, last year to date, and so forth). Some of the end dates are missing naturally, since some are still employed, so there's empty cells under "end date" column.

First off I should have a date table and join a relationship, right?

Second, I don't know where to begin with the DAX code. It would be so helpful if someone could advise on how to calculate this. Thank you!

[v-kelly-msft · Accepted solution]

Hi  @Meraki ,

Rather than creating a date table,I would prefer to recommend you tranform the table first in query editor:

Using below M codes to get a list for the year period:

Year=let _enddate=if [EndDate]=null then DateTime.FixedLocalNow() else [EndDate]
in
{Date.Year([StartDate])..Date.Year(_enddate)}

Then using below dax expression to get the months for each working year:

Month for each year =
VAR _enddate =
    IF ( 'Table'[EndDate] = BLANK (), TODAY (), 'Table'[EndDate] )
VAR _minyear =
    CALCULATE (
        MIN ( 'Table'[Year] ),
        FILTER ( 'Table', 'Table'[EmployeeID] = EARLIER ( 'Table'[EmployeeID] ) )
    )
VAR _maxyear =
    CALCULATE (
        MAX ( 'Table'[Year] ),
        FILTER ( 'Table', 'Table'[EmployeeID] = EARLIER ( 'Table'[EmployeeID] ) )
    )
RETURN
    IF (
        'Table'[Year] = _minyear,
        IF (
            'Table'[Year] <> _maxyear,
            DATEDIFF (
                'Table'[StartDate],
                DATE ( YEAR ( 'Table'[StartDate] ), 12, 31 ),
                MONTH
            ) + 1,
            DATEDIFF ( 'Table'[StartDate], _enddate, MONTH )
        ),
        IF (
            'Table'[Year] > _minyear
                && 'Table'[Year] < _maxyear,
            12,
            DATEDIFF ( DATE ( YEAR ( _enddate ), 1, 1 ), _enddate, MONTH ) + 1
        )
    )

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[TheoC]

Hi @Meraki 

If my understanding is correct, I believe you may want to use something like the following to 1. ensure that any EndDate that is blank is replaced with the maximum date (i.e. TODAY), and 2. that if there is an EndDate, it is used as the basis of end to the specific period.

Looking at your table of data, you also have SalaryMonthly data, therefore, it is likely that you may require the SalaryMonthly data to be multiplied against the period between the StartDate and EndDate?  If that is correct, then please use the following:

1. Create a Calculated Column that returns the number of days between the Start Date and End Date like below (note that you can adjust the _StartDate to whatever the period by amending the "_StartDate" variable.  If you have a Date / Calendar table, then I recommend linking it to that accordingly.

nDays = 

VAR _EndDate = IF ( ISBLANK ( 'Table'[End Date] ), TODAY () , 'Table'[End Date] )
VAR _StartDate = 'Table'[Start Date]

RETURN 

INT ( _EndDate - _StartDate )

2. Create a second Calculated Column that returns the amount for the respective period such as below:

AmtForPeriod = 

VAR _DailyRate = 'Table'[SalaryMonthly] * 12 / 365

RETURN 

'Table'[nDays] * _DailyRate

The output will look like below:

From here, if you want a measure, just create a new measure "Measure = SUM ( 'Table'[AmtForPeriod])" and it will give you what you want 🙂

Hope this helps 🙂

Theo

[selimovd]

Hey @Meraki ,

the easiest would be if you give an example how your data looks like. Even better if you have a sample files with just a few rows. Then it's the easiest to help you.

A description of the problem lets a lot of room for interpretation and many times it's easier to work with a small example.

In general, you should usually have a date table and connect it to your fact table.

You also need a proper date table in order for the time intelligence functions to work. Here is a small tutorial how to create a date table:
https://softcrylic.com/blogs/power-bi-for-beginners-how-to-create-a-date-table-in-power-bi/

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Meraki]

Hey @selimovd !

Thanks for replying.

I have created a date table now so that's fixed.

Here is a sample of how my data looks (this is something I created quickly in excel - format is the same but data is different)

So I'm stuck on how to calculate the salaries using DAX, in order to show a total sum in the report, for a given period (e.g. year to date, last year to date, or any given/selected year really).

Thank you!

[Ashish_Mathur]

Hi,

Share the link from where i can download the MS Excel file that you have created.  Also, if an employee leaves on October 8, 2021, will his/her salary to considered for the entire month of October?  Also, if an employee joins on August 27, 2018, will his/her salary to considered for the entire month of August?  Please clarify.