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 wo...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    JoshuaMartinezP Try this. PBIX is attached below signature.

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

     

JoshuaMartinezP's avatar
JoshuaMartinezP
Regular Visitor
3 years ago

'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's avatar
Greg_Deckler
Community Champion
3 years ago

JoshuaMartinezP Try this. PBIX is attached below signature.

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