How to filter work days of task based on date slicer

[PMnooBI]

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 

[MFelix]

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:

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]))
var days_total = NETWORKDAYS( startdatevalue, enddatevalue)
Return
IF(days_total > 0 , days_total)

[PMnooBI]

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 with@tamerj1 's solution but not with this.

[MFelix · Accepted solution]

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] )

[Anonymous]

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.

[tamerj1]

Hi @PMnooBI 

you may try

=

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

[PMnooBI]

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.

[tamerj1]

@PMnooBI 

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

[PMnooBI]

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

[tamerj1]

@PMnooBI 

Hope something like

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

[PMnooBI]

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

[tamerj1]

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