Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey,
I have a problem calculating the costs per day for employees within a time span with the following formular:
Salary / (working days ( weekdays - general holidays) - sum(vacation per employee per month))
I have a table:
employee name | entry date | |
Sarah | 01.05.2020 | |
Tom | 15.3.2020 |
another table
employee name | Month | salary |
Sarah | 31.05.2020 | 3000 |
Sarah | 30.6.2020 | 3000 |
Tom | 31.3.2020 | 1400 |
Tom | 30.4.2020 | 2800 |
Tom | 31.5.2020 | 2800 |
... | ... |
same with vacation
employee name | Month | vacation days |
Sarah | 31.05.2020 | =25/12 |
Sarah | 30.6.2020 | =25/12 |
Tom | 31.3.2020 | =30/12 |
Tom | 30.4.2020 | =30/12 |
Tom | 31.5.2020 | =30/12 |
... | ... |
I also have a Calendar which includes the working days - official holidays. with a column "workingdays/year" which have the nr 0 (no workingday) or 1 (workingday).
April 2020 | May 2020 | |
Sarah | - | 188 € |
Tom | 160 € (2800/(20-(30/12)) | 181 € |
HI @SarahAlsterspre,
I'd like to suggest you take a look at the following link if they suitable for your requirement.
Count of Number of days for Employees based on START & END dates
Regards,
Xiaoxin Sheng
Hey, thanks for your reply. It doesnt work with this extra slicer. If I use an extra calendar as a basis for a slicer in my report, the items and diagrams dont react. Any suggestions?
Hi @SarahAlsterspre,
maybe you can try to create a calculated table to expand the date ranges and link to original tables, then you can use this table date field as the slicer to filter original tables records and calculate with measure formula.
Spread revenue across period based on start and end date, slice and dase this using different dates
Regards,
Xiaoxin Sheng
Can you provide more details on your model, your data, and any error messages you are receiving? See this link for suggestions on how to give the community the information it needs to help: How to Get Your Question Answered Quickly
Also, make sure you're using measures not calculated columns with you formula. Sometimes a simple mix-up like that can lead to not expected results.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |