Forum Discussion
Calculate working days from multiple rows on the same date
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 Holidays is optional. If you can post sample data as text I can take a look.
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 |
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.
'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.JoshuaMartinezP Try this. PBIX is attached below signature.
No. of Work Days = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Table', [TS is work day] = TRUE),"Date",[Date])))