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
aempa
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
v-ljerr-msft
Employee
Employee

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
v-ljerr-msft
Employee
Employee

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

Thank you,

 

This is the perfect solucion.

ImkeF
Super User
Super User

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

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.