Calculate working days from multiple rows on the same date

[JoshuaMartinezP]

Hi,

I am using the day name from a Date column to work out whether it is a 'Work Day' or not:

2 measures are as follows:

TS Day Name = FORMAT('Vessel Support Data'[Date], "dddd")

TS is work day = IF('Vessel Support Data'[TS Day Name] IN {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday"}, true, false)

I then want to use these measures to find the number of work days relevant to my dataset:

No. of Work Days =

CALCULATE(

    COUNTA('Vessel Support Data'[TS is work day]),

    'Vessel Support Data'[TS is work day] IN { TRUE },

    DISTINCT('Vessel Support Data'[Date])

)

However, my DISTINCT() function doesn't appear to work. For example I get 34 work days in Jan which corresponds with the total number of rows for Jan:

Why does my 2nd filter not work? Is there an easier way to do this?

Thanks,
Josh

[Greg_Deckler]

@JoshuaMartinezP Use NETWORKDAYS?

[JoshuaMartinezP]

Hi Greg,

Thanks for the fast reply. 

NETWORKDAYS requires me to specify holidays etc. It also gives me the total number of all 'work days' but I want only the work days that have been worked on, as indicated by my row data.

If I use the row data then I will only pull through the work days of the month that need to be counted. Which is why I am trying to use a CALCULATE filter.

Thanks,

[Greg_Deckler]

@JoshuaMartinezP Holidays is optional. If you can post sample data as text I can take a look.

[JoshuaMartinezP]

Thank you, sample data as follows:

Date	Hours	TS Day Name	TS is work day
03-Jan-23	3	Tuesday	True
03-Jan-23	0.5	Tuesday	True
03-Jan-23	4	Tuesday	True
04-Jan-23	3	Wednesday	True
04-Jan-23	4.5	Wednesday	True
05-Jan-23	4	Thursday	True
05-Jan-23	2	Thursday	True
05-Jan-23	2	Thursday	True
06-Jan-23	4	Friday	True
06-Jan-23	4	Friday	True
09-Jan-23	3	Monday	True
09-Jan-23	1	Monday	True
09-Jan-23	3	Monday	True
10-Jan-23	3	Tuesday	True
10-Jan-23	2	Tuesday	True
10-Jan-23	1.5	Tuesday	True
10-Jan-23	3	Tuesday	True
11-Jan-23	3	Wednesday	True
11-Jan-23	4	Wednesday	True
11-Jan-23	0.25	Wednesday	True
12-Jan-23	2.5	Thursday	True
13-Jan-23	2	Friday	True
13-Jan-23	3	Friday	True
16-Jan-23	3.5	Monday	True
16-Jan-23	4	Monday	True
17-Jan-23	1	Tuesday	True
17-Jan-23	6.5	Tuesday	True
18-Jan-23	5	Wednesday	True
18-Jan-23	2.5	Wednesday	True
19-Jan-23	0.5	Thursday	True
19-Jan-23	3	Thursday	True
19-Jan-23	4	Thursday	True
20-Jan-23	3	Friday	True
20-Jan-23	4	Friday	True

[Greg_Deckler]

@JoshuaMartinezP Wait a minute, is that third calculation a calculated column or a measure? Because if it is a calculated column generally does not work with measures because the calculated column only gets calculated at the time of data load.

[JoshuaMartinezP]

'No. of Work Days' is a measure. 
'TS Day Name' and 'TS is work day' are calculated columns, built from the 'Date' column.

My aim is to display the number in a card that updates when I make a selection in my slicers. At the moment, because of my 'Date Table', the number updates with the dates correctly:

But selecting a slicer from my main dataset doesn't effect the 'Date Table' results and therefore the number of working days stays unchanged.
That's why I need to have the measure base the working days on the dates in the dataset. Unfortunately there are multiple rows per working day and so I need to find a way to filter them in the calculation.

[Greg_Deckler · Accepted solution]

@JoshuaMartinezP Try this. PBIX is attached below signature.

No. of Work Days = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Table', [TS is work day] = TRUE),"Date",[Date])))