Please check out the following thread, where I worked out with another member how to perform this same calculation:
DateDiff to only calculate number of work days - #19 by BrianJ
If you can please provide a bit more info on which end date to use (i.e., always use the latest date? always use the actual if there is one?), I can help you work out the specific DAX measure for your situation.
Hi @bimbbb,
By taking the idea from @BrianJ post, I have created model for you as per your requirments. Please find attached pbix file.
Idea is simple:
First created calculated column to count no of working days.
Noofdays Calculated_column =
CALCULATE (
SUM ( 'Date'[WorkDay] ),
DATESBETWEEN (
'Date'[Date],
'Main_table'[Start Date],
IF(ISBLANK('Main_table'[Last Day]),'Main_table'[Estimated Last Day],'Main_table'[Last Day])
)
)
now, after that i created Measure by using below formula:
Total working days =
CALCULATE(SUMX(VALUES('Main_table'),'Main_table'[Noofdays Calculated_column]))
and that’s it and got the result 
eDNA_Total working days.pbix (142.6 KB)
Nice work. I adapted your solution to subtract workday holiday days.
Step 1: Added Public Holidays table to model (no physical relationship)
Step 2: Added Calc Column to Date Table designating dates that are holidays as 1, otherwise 0
Holiday =
VAR IsHoliday =
LOOKUPVALUE(
'Public Holidays'[Holiday Binary],
'Public Holidays'[Date],
Dates[Date],
0
)
RETURN
IF( IsHoliday = 1, 1, 0)
Step 3: Added calculated column to remove workday holidays from total:
Workday Not Holiday =
SWITCH( TRUE(),
Dates[DayInWeek] = 5, 0,
Dates[DayInWeek] = 6, 0,
Dates[Holiday] =1, 0,
1
)
Then, adapted your measures to calculate total days using the Workday Not Holiday field from 3) above in place of of your workday field. Here’s the outcome:
Full solution file posted below.
Hope this is helpful.
using the Power BI web connector.
Hi @BrianJ, Great enhancement to the model and good learning for mr. I hope now solution is 100 % complete
Thanks Brian,
The end date depends on the person’s Off-Time Status. If they are back to work, then we use the actual but if the actual end is blank, it means they are not back, so we use the estimated date.
Abi
Perfect – that’s the decision rule that @hafizsultan and I used in our combined effort to provide a solution for you on this. Thanks for the clarification.
Please let us know if what we’ve provided meets your requirements.
Hi Brian
I’ll work with your solutions and let you know how it goes.
Thanks so much @BrianJ, @hafizsultan, @mickeydjw
Hi @hafizsultan
I tested the measures and the number of days are adding up from start date to end date as long as I don’t select a specific month, but I noticed that if the end date is in a different month from the start date and I select a specific month, for example, Jan 2020, the calculation does not count the number of days for the month selected, it counts the total number of days from start date to end date which will result in an incorrect count per month
Hi @bimbbb,
Thanks for sharing snapshot. Yes, current logic will not work in this case as we were getting number of working days between start date and end date. I will try to include above mentioned logic and get back to you.
Cheers!
Hi @bimbbb,
Please see if it serves your requirements.
Total Workdays Not Holidays_V2.0 =
SUMX (
FILTER (
ALL ( 'Dates' ),
'Dates'[Date] >= MAX ( 'Main_table'[Start Date] )
&& 'Dates'[Date] <= MAX ( 'Main_table'[Last_day_calc] )
&& 'Dates'[Date] <= MAX ( 'Dates'[Date] )
),
'Dates'[Workday Not Holiday]
)
Total Workdays_V2.0 =
SUMX (
FILTER (
ALL ( 'Dates' ),
'Dates'[Date] >= MAX ( 'Main_table'[Start Date] )
&& 'Dates'[Date] <= MAX ( 'Main_table'[Last_day_calc] )
&& 'Dates'[Date] <= MAX ( 'Dates'[Date] )
),
'Dates'[WorkDay]
)
In case, if you select particular month, it will count only that Month from start. Otherwise, it will count all dates.
eDNA_Total working days not incl holidays solution.pbix (201.9 KB)
Hopefully, it will resolve your issue
Hi @bimbbb , we’ve noticed that no response has been received from you since 22nd of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
I have tested the calculation and this is the result, I got. I also wanted to ask if this formula is supposed to give a total calculation or just a row by row total?
Thanks
Hi @bimbbb,
Thanks for testing. It’s all about context in which you want to use this formula. I have updated formula to get total right.
It will be for individual rows if you have start date & end date in table as these 2 are different dates. However, if you do not have startdate & lastdate in the table, it will be aggregated like below:
So, it all depends how do you want to use this formula and what should be the filter context.
Please check updated pbix file and see if it serves your purpose.
Total Workdays_V2.0 =
SUMX (
FILTER (
ALL ( 'Dates' ),
'Dates'[Date] >= MIN ( 'Main_table'[Start Date] )
&& 'Dates'[Date] <= MAX ( 'Main_table'[Last_day_calc] )
&& 'Dates'[Date] <= MAX ( 'Dates'[Date] )
),
'Dates'[WorkDay]
)
eDNA_Total working days not incl holidays solution V2.0.pbix (205.3 KB)
You are welcome @bimbbb . Also please note that all calculations will work based on start date as we have active relationship with start date.
I have tested it and it looks good so far, summing correctly based on month selected. I should be able to work on the active relationship for the date selections I want.
Big thanks for all your effort
Bimbbb
To the contributors of this post, thank you for all your inputs on this topic we are now tagging it as Solved. To help us learn more about your experience in the forum, please take a moment to answer this short forum survey. We appreciate all your help and suggestions. Thanks!