Forum Discussion
Datediff with filter
Good morning,
I have a table with a calendar, a column with the dates and a column with the number 1 for the days the company is open and 0 for the days the company is closed.
I have another table with a column with the start of production date and another with the end.
I need to datediff, filtering only the days the company is open, =1.I can't understand how this dont work:
Time Spent = CALCULATETABLE(datediff('Production'[Startdate],'Production'[Date Time],HOUR),FILTER(DateCalendar,DateCalendar[Workday]=1))
Joao_C_Faria , id start date and end date are column in same table :Production, you can create a column in Production
new column = countx(filter(DateCalendar, DateCalendar[Date] >= Production[Start Date] && DateCalendar[Date] <= Production[End Date] && DateCalendar[Workday]=1),DateCalendar[Date])
else you have to take a measure approch
How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA
4 Replies
Joao_C_Faria , create a new column in production
new column = countx(filter(DateCalendar, DateCalendar[Date] >= Production[Start Date] && DateCalendar[Date] <= Production[End Date] && DateCalendar[Workday]=1),DateCalendar[Date])
First of all, thank you for availability. Says the expression references multiple columns. It is not possible to transform several columns into a scalar value. I send the images as an attachment to make it easier, I changed the name of the columns that were in Portuguese to make it more readable. ComplimentsI need to know the number of business days between startdate and enddate.
Thank you very much.Joao_C_Faria , id start date and end date are column in same table :Production, you can create a column in Production
new column = countx(filter(DateCalendar, DateCalendar[Date] >= Production[Start Date] && DateCalendar[Date] <= Production[End Date] && DateCalendar[Workday]=1),DateCalendar[Date])
else you have to take a measure approch
How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA
The number 1 in the calendar table represents the company being open, and it's in text format, I tried to do "1", but it didn't work either.
