topic Re: How to filter work days of task based on date slicer in DAX Commands and Tips

How to filter work days of task based on date slicer

PMnooBI — Wed, 29 Mar 2023 14:06:55 GMT

As you can see I have a table visual with workdays of tasks. I would like to have another column that displays the work days within the date range set in the slicer. For example, the task on third row has 5 days worth of work since it starts on 29th and ends on 4th but the slicer is between 27th and 1st. Hence, I want another column displaying just 3 work days for that task as the work is being done on 29th 30th and 31st only. Please let me know how I can implement this even if a task ends or starts in between a set date range.

Thanks

The workday column is calculated as the difference between the start and end dates excluding the weekends

The date slicer has the calendar table date column added to it. The table visual displays the values from another data table called Assignments.

Greg_Deckler MFelix amitchandak Arul Ahmedx

Re: How to filter work days of task based on date slicer

tamerj1 — Wed, 29 Mar 2023 15:10:26 GMT

Hi PMnooBI

you may try

NETWORKDAYS ( MIN ( 'Date'[Date] ), MAX ( 'Date'[Date] ), 2 )

Re: How to filter work days of task based on date slicer

PMnooBI — Wed, 29 Mar 2023 15:59:24 GMT

That just returns the working days by excluding weekends. I need a function to calculate number of days of a task only within a date range.

Re: How to filter work days of task based on date slicer

tamerj1 — Wed, 29 Mar 2023 16:48:52 GMT

PMnooBI

Which date range? The selected date range or the date range between start and finish dates?

Re: How to filter work days of task based on date slicer

PMnooBI — Wed, 29 Mar 2023 16:50:33 GMT

Selected date range. the dates i set in the slicer. Basicaly have to calculate workdays that overlap with slicer date range and the task start and end dates

Re: How to filter work days of task based on date slicer

tamerj1 — Wed, 29 Mar 2023 17:15:53 GMT

PMnooBI

Hope something like

=
COUNTROWS (
INTERSECT (
CALENDAR (
MAX ( 'Table'[AssignmentSartDate] ),
MAX ( 'Table'[AssignmentFinishDate] )
),
VALUES ( 'Date'[Date] )
)
)

Re: How to filter work days of task based on date slicer

Anonymous — Wed, 29 Mar 2023 17:18:11 GMT

Hi PMnooBI

You can work around it:
CALCULATE(COUNT(DAYS), FILTER(DATES, SELECTEDVALUE(DATES(DATE)) >= MIN(DATE) && SELECTEDVALUE(DATES(DATE)) <= MAX(DATE))

Dax will be depend on that you are using a date table or not.

Re: How to filter work days of task based on date slicer

PMnooBI — Wed, 29 Mar 2023 17:57:46 GMT

Great! It does work but it counts the weekends as well. Can the formula be modififed to not include weekends? and also you can see the time is 12:00 a.m. for both slicer date ranges and I think that is why it counts the next day as another one

"Task Days" is the output

Re: How to filter work days of task based on date slicer

MFelix — Wed, 29 Mar 2023 18:05:16 GMT

Hi PMnooBI ,

Try the following code:

days_ = var maxselecteddate = MAX(Calendar[Date]) var minselecteddate = MIN(Calendar[Date]) var startdatevalue = MAX( minselecteddate , SELECTEDVALUE('Table'[AssigmentStartDate])) var enddatevalue = MIN( mAXselecteddate , SELECTEDVALUE('Table'[AssignementFinishDate])) Return NETWORKDAYS( startdatevalue, enddatevalue)

You can then make some adjustements to not get the negative values something similar to:

Re: How to filter work days of task based on date slicer

PMnooBI — Wed, 29 Mar 2023 20:27:09 GMT

Thanks! It did work but now for some reason the values won't add up and give me a total in the table visual shown in the image.

RESTCAP = Workperday*days_

My final goal is to find the total of these three columns. I was getting the sum withtamerj1 's solution but not with this.

Re: How to filter work days of task based on date slicer

tamerj1 — Thu, 30 Mar 2023 04:47:50 GMT

PMnooBI
Yes, the weekends can be filtered out using

= COUNTROWS ( FILTER ( INTERSECT ( CALENDAR ( MAX ( 'Table'[AssignmentSartDate] ), MAX ( 'Table'[AssignmentFinishDate] ) ), VALUES ( 'Date'[Date] ) ), NOT ( WEEKDAY ( [Date], 2 ) IN { 6, 7 } ) ) )

12:00AM is just a different format of 00:00 so I'm not sure if I fully understand what exactly the problem is.

Re: How to filter work days of task based on date slicer

MFelix — Thu, 30 Mar 2023 08:31:47 GMT

Hi PMnooBI ,

Try the following code:

days_ = VAR maxselecteddate = MAX ( Calendar[Date] ) VAR minselecteddate = MIN ( Calendar[Date] ) VAR startdatevalue = MAX ( minselecteddate, MIN ( 'Table (2)'[AssignementStartDate] ) ) VAR enddatevalue = MIN ( mAXselecteddate, MIN ( 'Table (2)'[AssignementFinishDate] ) ) VAR days_total = NETWORKDAYS ( startdatevalue, enddatevalue ) VAR TableValues = ADDCOLUMNS ( 'Table (2)', "DaysTotal", VAR net_days = NETWORKDAYS ( MAX ( minselecteddate, 'Table (2)'[AssignementStartDate] ), MIN ( mAXselecteddate, 'Table (2)'[AssignementFinishDate] ) ) RETURN IF ( net_days > 0, net_days ) ) RETURN SUMX ( TableValues, [DaysTotal] )