Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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
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
Solved! Go to 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]
)
)
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]
)
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
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 Try this: (1) Better Running Total - Microsoft Power BI Community
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?
Cheers,
Martin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |