I have a scenario where number of days between start and end date excluding weekends has to be calculated for each person in the data set.
The calculation is a bit straightforward for those who have only one record or row in the dataset but more complicated when an individual has more than one record in the dataset.
I need help with the total number of days calculation for those with multiple start and end dates in the same month and would prefer the calculation to sum all their records as one even though they appear more than once.
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.
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.
Brian eDNA_Total working days not incl holidays solution.pbix (185.9 KB)
.
P.S. I used Australian holidays in my Public Holiday table, because that’s what I had available from the previous example I worked up. However, it’s fairly straightforward to generate these by web scraping from sites like this
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.
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.
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
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.
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?
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]
)