topic Re: How to compute the yearly salary spendings/costs in DAX? in DAX Commands and Tips

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

Mar82 — Mon, 13 Mar 2023 07:14:38 GMT

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:

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).
Employee, who was hired before certain year and is terminated inside certain year. Salary needs to be calculated just for active months (EmpID: 4).
Employee, who was hired in certain year and is active till end of certain year (EmpID: 3).
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

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

Greg_Deckler — Mon, 13 Mar 2023 08:29:45 GMT

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

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

Mar82 — Mon, 13 Mar 2023 18:29:21 GMT

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

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

tamerj1 — Mon, 13 Mar 2023 19:46:55 GMT

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]
)

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

Mar82 — Tue, 14 Mar 2023 06:04:17 GMT

Hitamerj1

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

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

tamerj1 — Tue, 14 Mar 2023 06:22:36 GMT

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] ) )

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

Mar82 — Tue, 14 Mar 2023 06:47:31 GMT

Hi tamerj1 ,

Really nice, now I got the card I wanted.

I appreciate your help.

Cheers,

Martin