Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
mstefancik
Advocate II
Advocate II

DAX Formula : Number of days in month

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:

 model.PNG

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.

result.PNG

 

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

 

5 REPLIES 5
Eric_Zhang
Employee
Employee


@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:

 model.PNG

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.

result.PNG

 

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?

@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 osample1.PNGf the day i get actual monthly leave report.

 

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.

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. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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.

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors