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

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

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

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.

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

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.

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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