Calculate the working days between the two dates that have been selected in the slicer in power bi

0
votes

I want to create a measure that counts the working days between two dates that have been selected in a slicer. For now I can calculate the days between the selected dates, but I cannot exclude the weekend days.

Days =
/*We get the first and last date from the slicer*/
VAR First = CALCULATE ( MIN ( Date[Date] ), ALLSELECTED ( Date[Date] ) )
VAR Last =  CALCULATE ( MAX ( Date[Date] ), ALLSELECTED ( Date[Date] ) )
/*We calculate the days between the two dates*/
RETURN
    DATEDIFF ( First, Last, DAY )

Photo

1
powerbidaxpowerpivot

1 Answers

0
votes

I'd recommend adding a column IsWorkingDay to your date table first. As explained here, you can exclude weekends with a column like this:

'Date'[IsWorkingDay] = NOT WEEKDAY( 'Date'[Date] ) IN { 1,7 }

where 1 and 7 correspond to Sunday and Saturday, respectively.

Once you have that, it's just a matter of counting days where that column is true.

As a measure, you could write this as

WorkingDaysCount =
COUNTROWS (
    FILTER ( 'Date', 'Date'[IsWorkingDay] = TRUE () )
)

Note that the table 'Date' should already be filtered by the slicer and you're just adding one extra condition. If you have additional filter context that you need to ignore, then you may want to use ALLSELECTED ( 'Date' ) instead of just 'Date'.