Calculation with 2 date ranges that have no active relationship

[Tingeling]

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?

[amitchandak · Accepted solution]

Refer, if this can help. How to deal with start and end date

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos.

[Tingeling]

Hi,

Thank you for your quick response - 

Here is our code, not that different from yours when working with inactive relationships:

Sum Salary = 

CALCULATE(

        SUM('Salary'[Amount]),

FILTER(

ALL(Salary[SalaryFromDateId]),

MAX('Dates'[DateId]) >= SalaryFromDateId

),

FILTER(

ALL(Salary[SalaryEndDateId]),

MAX('Dates'[DateId]) <= SalaryEndDateId

)

)

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

[Icey]

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

[Tingeling]

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