Calculating fixed salary based on worked days

[Anonymous]

Hi all,
I'm trying to calculate fixed salary of delivery personel.
I choosed the person and filtered dates

Total number of working days in the month = CALCULATE(COUNTX('courier grafic','courier grafic'[time_beg]))

Total actual worked days = CALCULATE(COUNTX('courier grafic','courier grafic'[t1f]))

Fixed salary based on actual worked days  = [Salary]*[Total actual worked days]/[Total number of working days in the month]

salary = 139105
Total number of working days in the month = 23
Total actual worked days = 12
Answer have to be =72577

But when I filter dates I also filter (limit) "Total number of working days in the month", is there any way to correct this formula that if I filter dates formula somehow undestands that it should take total number of days in that month but not filtered? 

date	time_beg	t1f
01.03.2021 0:00	9:00:00
02.03.2021 0:00	9:00:00	9:00:00
03.03.2021 0:00	9:00:00	9:00:00
04.03.2021 0:00	9:00:00	9:00:00
05.03.2021 0:00	9:00:00	9:00:00
06.03.2021 0:00		9:00:00
07.03.2021 0:00		9:00:00
08.03.2021 0:00	9:00:00	9:00:00
09.03.2021 0:00	9:00:00
10.03.2021 0:00	9:00:00	9:00:00
11.03.2021 0:00	9:00:00	9:00:00
12.03.2021 0:00	9:00:00	9:00:00
13.03.2021 0:00		9:00:00
14.03.2021 0:00		12:00:00
15.03.2021 0:00	9:00:00	9:00:00
16.03.2021 0:00	9:00:00	9:00:00
17.03.2021 0:00	9:00:00	9:00:00
18.03.2021 0:00	9:00:00	9:00:00
19.03.2021 0:00	9:00:00	9:00:00
20.03.2021 0:00		9:00:00
21.03.2021 0:00		9:03:53
22.03.2021 0:00	9:00:00	9:00:00
23.03.2021 0:00	9:00:00	9:00:00
24.03.2021 0:00	9:00:00	9:00:00
25.03.2021 0:00	9:00:00	9:00:00
26.03.2021 0:00	9:00:00	9:00:00
27.03.2021 0:00		9:00:00
28.03.2021 0:00		9:00:00
29.03.2021 0:00	9:00:00	9:00:00
30.03.2021 0:00	9:00:00	9:00:00
31.03.2021 0:00	9:00:00	9:00:00

@Ashish_Mathur 

@Greg_Deckler 

@Jihwan_Kim 
@marcorusso 

@AlB 

[Munawar1881]

Hello Power BI Community, I hope this message finds you well. I wanted to reach out and share a situation I'm facing with my data, hoping to get some guidance and insights from this wonderful community. Here's the scenario: I have a dataset that includes a date column ranging from January 1st to June, an employee name column with 320 employee names, an employee status column indicating "Active" or "Inactive," and net salary recorded on the 1st day of each month. The remaining rows for each month are empty for net salary. My goal is to calculate the work days for each employee based on their status, considering only those with an "Active" status. If an employee is marked as "Terminate," they should not be counted as working days. Additionally, I would like to create a new column that calculates the net salary per day for each employee. o tackle this challenge, I have started exploring Power BI and utilizing DAX measures. Here are the measures I have developed: Work Days Count: Work Days Count = CALCULATE( COUNTROWS('YourTable'), 'YourTable'[Employee Status] = "Active", 'YourTable'[Net Salary] <> BLANK(), 'YourTable'[Net Salary Day] <> BLANK() ) Net Salary by Day: Net Salary by Day = IF( 'YourTable'[Employee Status] = "Active", 'YourTable'[Net Salary] / 'YourTable'[Work Days Count], BLANK() ) would greatly appreciate any feedback, suggestions, or alternative approaches from the community. If there are other measures or techniques that can help me achieve accurate work days and net salary calculations, please don't hesitate to share them. Thank you in advance for your support and expertise. I look forward to hearing from you and learning from your valuable insights.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result.

[Munawar1881]

Expected Result Example

Attendance Date Attendance Status EmployeeStatus BranchName NetSalary EmpCategory Emp.No ProjectNo Employee No. of Days Daily Salary Per Day

01/01/2023 O Active City Lab 25,000 Management 1220078 GP/0001 Muhammad Shahzaib 18 1388.888889 1388.888889

02/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

03/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

04/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

05/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

06/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

07/01/2023 O Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

08/01/2023 O Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

09/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

10/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

11/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

12/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

13/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

14/01/2023 O Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

15/01/2023 O Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

16/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

17/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

18/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

19/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

20/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

21/01/2023 O Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

22/01/2023 O Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

23/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

24/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

25/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

26/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

27/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

28/01/2023 O Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

29/01/2023 O Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

30/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

31/01/2023 P Active City Lab Management 1220078 GP/0001 Muhammad Shahzaib 1750

01/02/2023 P Active City Lab 35,000 Management 1220078 GP/0001 Muhammad Shahzaib 20 1750 1750

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

I am not sure how your data model looks like, but if I assume you created your date-slicer by using the column from Courier Grafic Table, please try the below measure.

Total number of working days in the month =
VAR currentmonth =
MONTH ( MAX ( 'courier grafic'[date] ) )
RETURN
CALCULATE (
COUNTX ( 'courier grafic', 'courier grafic'[time_beg] ),
FILTER (
ALL ( 'courier grafic'[date] ),
MONTH ( 'courier grafic'[date] ) = currentmonth
)
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/