cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aempa Frequent Visitor
Frequent Visitor

Salary expenses with Time intervals

Hello,

 

I would like to know if you can help me. I need to know my monthly salaries expenses in the last 10 years and the evolution of the average salary in my company.

 

In my database, each employee has several lines with the registration of the various salaries they have received over time.

 

Employee

Start

End

Salary

1

01/01/2005

31/12/2005

 1 900,00 €

1

01/01/2006

31/12/2006

 1 950,00 €

1

01/01/2007

31/12/2007

 2 000,00 €

1

01/01/2008

31/12/2015

 2 050,00 €

1

01/01/2016

31/12/9999

 2 100,00 €

2

01/01/2014

31/12/2015

 5 250,00 €

2

01/02/2010

31/12/2013

 2 000,00 €

2

01/01/2016

31/12/9999

 5 250,00 €

3

01/10/2008

31/12/2009

 950,00 €

3

01/01/2010

31/03/2010

 1 000,00 €

3

01/04/2010

30/09/2010

 1 200,00 €

3

01/10/2010

31/07/2011

 1 390,00 €

3

01/03/2014

30/04/2015

 2 680,00 €

3

01/08/2011

28/02/2014

 2 600,00 €

3

01/05/2015

31/12/2015

 4 000,00 €

3

01/01/2016

31/12/9999

 4 500,00 €

 

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-ljerr-msft
Microsoft

Re: Salary expenses with Time intervals

Hi @aempa,

 

According to your description, you should be able to follow steps below to show the monthly salaries expenses and get the evolution on the Line Chart visual.

 

1. Add an individual Calendar table if you don't have one yet.

Date = CALENDARAUTO()

2. Use the formula below to create a measure, and show the measure on the Line visual with Date[Date] column as Axis, Employee column as Legend.

CurrentSalary = 
VAR currentDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        MAX ( Table1[Salary] ),
        FILTER ( Table1, Table1[Start] <= currentDate && Table1[End] >= currentDate )
    )

r2.PNG

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

View solution in original post

3 REPLIES 3
Super User III
Super User III

Re: Salary expenses with Time intervals

This article might help you: http://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Microsoft v-ljerr-msft
Microsoft

Re: Salary expenses with Time intervals

Hi @aempa,

 

According to your description, you should be able to follow steps below to show the monthly salaries expenses and get the evolution on the Line Chart visual.

 

1. Add an individual Calendar table if you don't have one yet.

Date = CALENDARAUTO()

2. Use the formula below to create a measure, and show the measure on the Line visual with Date[Date] column as Axis, Employee column as Legend.

CurrentSalary = 
VAR currentDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        MAX ( Table1[Salary] ),
        FILTER ( Table1, Table1[Start] <= currentDate && Table1[End] >= currentDate )
    )

r2.PNG

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

View solution in original post

aempa Frequent Visitor
Frequent Visitor

Re: Salary expenses with Time intervals

Thank you,

 

This is the perfect solucion.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors