How to compute the yearly salary spendings/costs in DAX?

[Mar82]

Hi there,

As a part of a Power BI HR Dashboard, I want to showcase the yearly salary costs in a card visual as a rolling total per year. How I can transfer this to DAX code?

1. Result to achieve:

Example: Test data for year 2018:

Card content

Cost of Salary per certain year

32.000 €

Option to change specific year by slicer on Date Table

Columns to work with in fact table

• EmpID
• Salary
• DateofHire
• TermDate
• SalaryMonthly

2. Things to consider:

1. Employee, who was hired before a certain year and is active till end of certain year. Salary spendings needs to be calculated over all month of year (EmpID: 1).
2. Employee, who was hired before certain year and is terminated inside certain year. Salary needs to be calculated just for active months (EmpID: 4).
3. Employee, who was hired in certain year and is active till end of certain year (EmpID: 3).
4. Employee, who was hired in certain year and is terminated inside certain year (EmpID: 2).

3. Data Model

3.1 Relationships

Date Table & Fact table is connected via inactive relationships

‘Date’[Date] 1->* ‘Fact’[DateofHire]

‘Date’[Date] 1->* ‘Fact’[TermDate]

4. Data Set

https://1drv.ms/u/s!ApbrVj8RZyxnkWev_fGDrZQdMhls?e=K3hPy5

I would appreciate, if someone can help me out.

Cheers,

Martin

[tamerj1]

Hi @Mar82 

assuming both relationships are inactive, you may try

Salary Amounts =
VAR MinDate =
MIN ( 'Date'[Date] )
VAR MaxDate =
MAX ( 'Date'[Date] )
RETURN
SUMX (
FILTER ( 'Fact', 'Fact'[DateofHire] <= MaxDate && 'Fact'[TermDate] >= MinDate ),
'Fact'[SalaryMonthly]
)

[Mar82]

Hi@tamerj1 

Thanks a lot for your answer. I have added a FILTER argument to include also the Blanks for 'Fact'[TermDate].

Salary Amount :=

    VAR MinDate = MIN('Date'[Date])

    VAR MaxDate = MAX('Date'[Date])

RETURN

    SUMX(

        FILTER(

            'Fact',

                'Fact'[DateofHire] <= MaxDate && 'Fact'[TermDate] >= MinDate ||

                'Fact'[DateofHire] <= MaxDate && 'Fact'[TermDate]=BLANK()  // including TermDate Blanks //

        ),

        'Fact'[SalaryMonthly]

    )

Unfortunately I just have a  single value for column [SalaryMonthly] per each employee even the person is employed multiple months.

As a result, the [Salary Amount] for e.g. a certain year is computed as if all the employees would have get just one single monthly salary paid out in that specific year.

How I can solve this?

Thanks for your support.

Cheers,

Martin

[tamerj1 · Accepted solution]

Hi @Mar82 
This has to be a measure. It cannot be a calculated column. Please refer to attached amended sample file.

To handle blanks please use

Salary Amounts = 
SUMX (
    VALUES ( 'Date'[Year Month] ),
    VAR MinDate = CALCULATE ( MIN ( 'Date'[Date] ) )
    VAR MaxDate = CALCULATE ( MAX ( 'Date'[Date] ) )
    RETURN
        SUMX (
            FILTER ( 'Kaggle', 'Kaggle'[DateofHire] <= MaxDate && COALESCE ( 'Kaggle'[TermDate], TODAY ( ) ) >= MinDate ),
            'Kaggle'[SalaryMonthly]
        )
)

[Mar82]

Hi @tamerj1 ,

Really nice, now I got the card I wanted.

I appreciate your help.

Cheers,

Martin

[Greg_Deckler]

@Mar82 Try this: (1) Better Running Total - Microsoft Power BI Community

[Mar82]

@Greg_Deckler 

Hi Greg,

thanks a lot for your reply and the link you provided. An entertaining rant on Calculate 😉

Unfortunately my starting point is a bit different from the example in your video.

First of all, I don't have the values ( in my case monthly salary) per each month.I just have it one time in the table per employee (EmpID).

Also I need to consider  if  a person ( based on their Hire or Term Date) is still employeed in a specific year and if so how many month.

May you have an idea to solve my issue?

"How to compute the yearly salary spendings/costs in DAX?"

Cheers,

Martin