Forum Discussion

JoshuaMartinezP's avatar
JoshuaMartinezP
Regular Visitor
3 years ago
Solved

Calculate working days from multiple rows on the same date

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

Need Help

7 Replies

    • JoshuaMartinezP's avatar
      JoshuaMartinezP
      Regular Visitor
      3 years ago

      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,