Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tingeling
Frequent Visitor

Calculation with 2 date ranges that have no active relationship

Hi all,

 

We are trying to SUM Salary per amployee across department and time. We have the following relationships in our dataset:

SalaryUML.PNG

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:

Salary_Tabell.PNG

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?

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

4 REPLIES 4
amitchandak
Super User
Super User

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.

 

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
Community Support
Community Support

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

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.