Salary expenses with Time intervals

[aempa]

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 €

[v-ljerr-msft · Accepted solution]

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 )
    )

Here is the sample pbix file for your reference. 

Regards

[aempa]

Thank you,

This is the perfect solucion.

[ImkeF]

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