DAX Formula : Number of days in month

[mstefancik]

Hello,

I would need help with DAX formula.

Scenario:

I have got 3 tables:

1. Users - list of employees

2. Calendar - calculated calendar created with DAX (including columns for isholiday, isweekend)

3. Absences - list of all employees leaves (Employee, Type of leave, From, To)

Model:

 

What I need to achieve is to get result as shown below. The monthly report, where Leave days is count of leave working days in actual month. In this example for July.

For July and Employee E - actual leave days count is 1, although the length of vacation is 5 days.

For July and Employee D - actual leave days count is 6, although the length of vacation is 10 days (difference between From and To dates is 12, but there is also weekend, so minus 2 days)

But for August and Enmployee D I need to take into consideration 4 days, which should be added to his leave days count in August.

Could anybody give me an advice how to achieve described results?

Thanks

[Eric_Zhang]

---

@mstefancik wrote:

Hello,

I would need help with DAX formula.

Scenario:

I have got 3 tables:

1. Users - list of employees

2. Calendar - calculated calendar created with DAX (including columns for isholiday, isweekend)

3. Absences - list of all employees leaves (Employee, Type of leave, From, To)

Model:

 

What I need to achieve is to get result as shown below. The monthly report, where Leave days is count of leave working days in actual month. In this example for July.

For July and Employee E - actual leave days count is 1, although the length of vacation is 5 days.

For July and Employee D - actual leave days count is 6, although the length of vacation is 10 days (difference between From and To dates is 12, but there is also weekend, so minus 2 days)

But for August and Enmployee D I need to take into consideration 4 days, which should be added to his leave days count in August.

Could anybody give me an advice how to achieve described results?

Thanks

---

@mstefancik

For counting workdays only, you can reference this thread. As to counting days in Jul and Aug individually, could you post any sample data and expected output?

[mstefancik]

@Eric_Zhangof course, see attached sample data.

There are three tables. Just for explananation. Users and Calendar are clear. In absences table you can find records for every absence employees took, type of leave, leave start and end date and total leave days count (working days).

What I need to achieve is in the Results table.

When the leave was taken within one moth (employees A,B,C,D) the result is pretty clear. In fact is the same as the Total leave days count in Absence table.

But when the length of leave is not within the same month (employees D,E- the leave starts in july and ends in august) i need to get Leave days count but for the month July for employee D is 6 and for employee E is 1. The rest of leave days goes to the next month. So for August report employee D has got 4 days and employee E 4 days.

At the end of the day i get actual monthly leave report.

[mstefancik]

I have found article about How many working days has been an employee been off work originaly posted on powerpivotpro.

Powerpivot Pro article.

Presented dax formula works for me, but i get the same results as are stored in the Leave Days column in Absences table.

 CALCULATE(SUM('Calendar'[Is working day]);
            FILTER (
            'Calendar';
            'Calendar'[Date] >=  MAX ( Absences[From] ) &&
            'Calendar'[Date] <= MAX ( Absences[To] )
                   ))

I would need to adjust this formula to get the reulst i expect.

[MattAllington]

You haven't described what is wrong. I note that the model in he OP has relationships - my example on PPP does not have relationships. 

[mstefancik]

Your formula is correct. But I would need to get results as described:

Example for Empoyee 😧 the length of his vacation is from 24.7. - 4.8. and it makes 10 working days. i can get this by your formula.

What I need to get moreover is

7/2017 report - record for employee D and his vacation but only working days for July, which makes 6 days

8/2017 report - record for employee D and his vacation but only working days for August, which makes 4 days

I have got relationships in my model as I have got other reports which need this relationships.

I can duplicate Absence table for this report if relationship does not need to be there. But how should I filter report to get the correct results for monthly report.

So once the vacation is across 2 months I would like to see this record in both months with correct number of working days.