Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all,
We are trying to SUM Salary per amployee across department and time. We have the following relationships in our dataset:
So far we have managed to sum salary per employee based on the two dates in table 'Salary', [SalaryStartDateId] and [SalaryEndDateId]. We have managed to "fill out" the months between the Start and the End date with the annual salary so that our results can be shown as followed:
Employee1: [SalaryStartDateId] = 20180101 and [SalaryEndDateId] = 20191231
Employee2: [SalaryStartDateId] = 20170101 and [SalaryEndDateId] = 20190505
Employee3: [SalaryStartDateId] = 20170606 and [SalaryEndDateId] = 20180808
We would like to show Sum Salary in a Line Chart in order to show Sum salary across time and department. When dragging "Department" in to the line chart it seems that SUM uses MIN(SalaryStartDateId) and MAX(SalaryEndDateId) across all employees instead of using both the dates for each row/employee. The result is flat lines across time and the wrong total sum.
Any suggestion to how we should solve our problem?
Solved! Go to Solution.
Refer, if this can help. How to deal with start and end date
Appreciate your Kudos.
Refer, if this can help. How to deal with start and end date
Appreciate your Kudos.
Hi,
Thank you for your quick response -
Here is our code, not that different from yours when working with inactive relationships:
Sum Salary =
We might try creating a new "on the fly" table containing our dates to see how we go. Originally we wanted to avoid this seeing we are working on a cube and not a DB. We had an idea that creating code avoiding additional tables for dates would simplify our code in the long run - we might be wrong...
Regards
Linn
Hi @Tingeling ,
Is this problem solved?
If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If not, please let me know and share me a dummy PBIX file for test.
Best Regards
Icey
Hi,
So creating an examplefile for you made us realize that our kink lies in the use of our cube - our code works fine. We do not yet understand why it doesnt work and have to dig a little deeper in order to fix our report. Thank you for your feedback and I have now marked the first replay as the solution as we know that code works.
tnx
Linn
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
108 | |
107 | |
91 | |
67 |
User | Count |
---|---|
162 | |
133 | |
132 | |
93 | |
91 |