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
moizsherwani
Continued Contributor
Continued Contributor

Salaries within a time period

Hey everyone,

 

So here is my problem, I need to calculate (using Measures in DAX) the salaries that were paid in x duration of time, let's for example assume between Jan 1st 2017 and June 30th 2017. So there are four types of employees

 

1) An Employee who joined before Jan 1st 2017 and are still employed on June 30th

2) An Employee who joined before Jan 1st 2017 and quit before June 30th (for example April 15th)

3) An Employee who joined after Jan 1st (for example March 15th) and are still employed on June 30th

4) An Employee who joined after Jan 1st (for example May 15th) and quit before June 30th (for example June 15th). In tabular form it would be as below

 

EMP - STARTDATE - ENDDATE - SALARYPERMONTH

1     -   1/1/2016    - BLANK       -400

2     -    6/1/2016   - 4/15/2017  - 200

3     -    3/15/2017 - BLANK        - 300

4     -    5/15/2017 - 6/15/2017   - 100

 

Now to calculate the salaries between Jan and June for all employees we would take

 

EMP - MONTHS - TOTALSALARY

1      -  6              - 6 x 400 = 2400

2      -  3.5            - 3.5 x 200 = 700

3      -  3.5            - 3.5 x 300 = 1050

4     -   1               - 1 x 100 = 100

 

The Generic Formula would be 

 

Total Salary For All Emp = TotalSalaryForEachEmployee x monthsworked (during Jan and June 2017)

 

Can anyone help me convert this to DAX?

 

Note: Anyone who worked half a month only gets half a months salary, one who worked quarter of a month a quarter salary. 

 

Regards,

 

Moiz

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.
10 REPLIES 10
Anonymous
Not applicable

Hi @moizsherwani

 

I come with a solution.

 

DISCLAIMER: It's important to notice that this solution only accounts for full month pay or half month pay. It cannot handle cases where an employee has been employed at one quarter of a month or at 3 quarters of a month. It cannot handle cases, where an employee stops employment one quarter into a month or 3 quarters into a month, either. It is possible to make, but it will take me a lot of time, so I skipped these steps. This example (solution) is simply just to illustrate, how you can go about solving your challenge.

 

I went through a lot of DAX to solve this so please take your time to go through the DAX formulas, and realize how they were constructed. I'll also gladly elaborate on any of the steps I went through.

 

My method was to create a start date (for the payment period) this year and an end date (for the payment period) this year. Then I wanted to find the amount of months within these periods and times it with the salary of a given employee.

 

This first thing I did was creating an independent time dimension. I go to the menu and select modelling. I then select "New Table". That will send me to a DAX formula.

 

I used this calculation to construct my time dimension:

TimeDim = CALENDAR(FIRSTDATE(Test[Start date]);NOW())

 

I have not related the time dimension to the data, because this will mess up my calculations, but the time dimension is still useful, which you'll see in my first calculation.

 

I then proceeded to create start date this year. The Dax calculation looks like this:

Start date this year =

IF(AND(YEAR(Test[Start date])=YEAR(LASTDATE(TimeDim[Date])); Test[Start date]<>DATE(YEAR(LASTDATE(TimeDim[Date]));1;1));DATE(YEAR(LASTDATE(TimeDim[Date]));MONTH(Test[Start date]);DAY(Test[Start date]));DATE(YEAR(LASTDATE(TimeDim[Date]));1;1))

 

Nest step from there was to create end date this year. The DAX calculation looks like this:

End date this year = 

IF(AND(ISBLANK(Test[End date]); YEAR(Test[Start date])=YEAR(DATEADD(LASTDATE(TimeDim[Date]);-1;YEAR))); ENDOFMONTH(DATEADD(LASTDATE(TimeDim[Date]);-1;MONTH));
IF(AND(ISBLANK(Test[End date]);AND(YEAR(Test[Start date])=YEAR(LASTDATE(TimeDim[Date]));DAY(Test[Start date] )=15));ENDOFMONTH(DATEADD(LASTDATE(TimeDim[Date]);-1;MONTH));Test[End date]))

 

And then the most tricky part of the process, the difference between end date this year and start date this year:

Salary months this year =
IF(AND(DAY(Test[Start date this year])=1;MONTH(Test[Start date this year])=1);
IF(DAY(Test[End date this year])>=28;MONTH(Test[End date this year]);
IF(AND(DAY(Test[Start date this year])=15;DAY(Test[End date this year])=15);BLANK();
IF(AND(DAY(Test[Start date this year])=1;DAY(Test[End date this year])=15);MONTH(Test[End date this year]) - 0,5;0)));
IF(AND(DAY(Test[Start date this year])=15;DAY(Test[End date this year])=15);MONTH(Test[End date this year]) - MONTH(Test[Start date this year]);IF(AND(DAY(Test[Start date this year])=15;DAY(Test[End date this year]) >=28);MONTH(Test[End date this year]) - MONTH(Test[Start date this year]) + 0,5;BLANK())))

 

The last calculation, total salary, is simple enough. It looks like this:

Total Salary = SUM(Test[Salary]) * SUM(Test[Salary months this year])

 

It's important to notice that Start date this year, End date this year and Salary months this year are all calculated columns, while total salary is a measure.

 

The only place, where you might run into problems, is if the TimeDim exceeds the original end date with more than 1 month. So be aware of this.

 

I hope this brings you somewhat closer to an end-goal-solution. You can see the results on the picture below. 

 

Again, I'll gladly elaborate on anything you have questions for. This method is pretty complex, so by any means please ask.

 

Best,

Martin

 

Sales within a time period.png

Anonymous
Not applicable

 

I realized during the weekend that I had made a mistake. The 3rd employee didn't start 15th march 2016, this employee started 15th march 2017. So I've added this case to the code. I've edited this into the above post, where you'll find the solution.

 

@moizsherwani I'd like to follow up on whether this solves your problem? I'd also like some feedback on it. I spent a great deal of time developing this.

 

Best,

Martin

@Anonymous thanks so much for taking the time to get back with this solution, at first glance it seems to get the solution but with more steps than I had hoped for but so I cannot say right away whether it will resolve the issue but I will be reviewing this next week and will get back to you.

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.
Anonymous
Not applicable

@moizsherwani

 

I just want to follow up again. Did you go anywhere with my solution? 🙂

@Anonymous: I am extremely sorry for the delay in responding back, the project went on the back burner and so I got pulled away into other projects but is now back up and running. So one of the requirments was to be able to adjust the date using a date slicer so it is not that I need to calculate the salary for this year only but it could be any range of time I selected, so for example it is Jan 2018 but I wanted to see how much salary we paid from Mar 2017 to Jun 2017 or any time period, would your proposed solution be able to tackle this requirement?

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.
Anonymous
Not applicable

@moizsherwani Don't worry the least. Your business is naturally the most important thing to you as my business is the most important thing to me. I'm in a very busy period an as such i propose that you follow @fhill's post and see if you can go anywhere with that. If you report back to this post and you think that there is a good case for continuing with my solution I'll be more than happy to look more into it. I think we can easily reach a solution but i will be occupied for the next week or so.

Anonymous
Not applicable

Hi @moizsherwani

 

@fhill gave an example on how to accommodate your challenge. I am working on a solution, which gives you the results in the table format, you've asked for. I won't finish until tomorrow, so please have patience. By tomorrow, I'll have the solution, you desired in your original post.

 

Best,

Martin

fhill
Resident Rockstar
Resident Rockstar

In PowerBI there are always multiple ways to complete a goal.  This is one solution that works the way I tend to program.

 

First, we'll need to convert your Start / End dates into a List of dates so we can Slice / Filter off them to find time ranges.  See this posting where a Number.From formula is used.  I also have the code in the first screen shot below.  This creates a new Custom Column of LIST of dates between start and end.  I had to modifiy the code to include a 'Use Today's Date if NULL' logic.   *** Haven't tested this yet to see if it works tomorrow, but you may need to do a Data Refresh for the changes to take effect to insert new days as time passes? ***

 

https://community.powerbi.com/t5/Desktop/Getting-all-dates-between-2-dates/m-p/175177?lightbox-messa...

 

If you click the Double Arrows on the DateList column, you can choose to 'Expand Dat List' which now shows every day an exmployee is employed.  Since people can be hired and fired more than the First and Last of each month, I used a Power BI Tool called 'Date' 'Month' - 'Days in Month' to count the number of days each month.  

 

My 3rd screen shot shows the custom column code to divide slary by Days in a month.  ** P.S.  Don't forget to make your Salary and Days in Month column whole numbers.  And after the calculation make SalaryPerDay a decimal.

 

Now you can create visuals as desired based on the 'DateList' column and a SUM of SalaryPer Day.  I have a few samples below where the slicers only interact with the table to their left.

 

Thank You,

FOrrest

 

 

Capture.PNGCapture2.PNGCapture3.PNGCapture4.PNG




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




moizsherwani
Continued Contributor
Continued Contributor

@fhill thanks for your reply and boy am I late in responding back 🙂 (actually the project got put on the back burner but has now restarted), the solution you suggested (though it works) is probably not optimal because the company has hundreds of employees (some of who have been working for over a decade) so to create a single row for each day of their employment would result is quite a massive table (so for example 800 employees and on average each has worked 5 years would mean 800 x 5 x 365 = 1.4 million rows

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

I completed this 'summary by month' code a while back.  Review it and see if something simular could possibly help your situation?  Hope it helps, FOrrest

 

https://community.powerbi.com/t5/Desktop/Case-Backlog-Age/td-p/280663

 




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




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.