Forum Discussion

Tingeling's avatar
Tingeling
Frequent Visitor
6 years ago
Solved

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:

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?

 

Need Help
Show and Tell
Tips and Tricks

4 Replies

    • Tingeling's avatar
      Tingeling
      Frequent Visitor
      6 years ago

      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's avatar
        Icey
        Icon for Community Support rankCommunity Support
        6 years ago

        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