Forum Discussion
Count days for each week
Hi everyone,
I hope you're well and I wish you an happy new year!
I looked for my issue on the forum but I don't really know how to look for this issue...
I got a table with a start date and a end date for each row. I also got a related Date Table.
I would like the count of days for each week.
Ex : If I got a start date on 1/01/2022 and end date on 31/12/2022, I would like to have wk1=5, wk2=5, wk3=5 wk4=5, wk5=1
the initial table don't content all the dates between 01/01/2022 and 31/12/2022 but just these 2 dates.
I hope you understand my request...
Thanx for all!!
Hi Anonymous ,
I think you want to select the cars based on the slicer, and then display the number of days that the selected car appears on the year-week axis.
My sample data
The main table has start dates, end dates and different cars.
The calendar table has dates, year-weeks, and a sort column sorted by the year-week column.
1.Create the first measure, make it all dates between the start date and end date, return 1.
Measure = IF(MAX('Calendar'[Date])<=MAX('Table'[end date])&&MAX('Calendar'[Date])>=MAX('Table'[start date]),1)2.Create another measure that sums the first measure.
Measure 2 = COUNTROWS(FILTER('Calendar',[Measure]=1))Put the year-week column together with the second measure to get the result.
You can select cars based on the slicer to display results for different cars.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
12 Replies
- amitchandak
Super User
Anonymous , I think you need workdays
a measure
Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Min(Table[Start Date]),Max(Table[End Date])),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))
a column
Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Start Date],Table[End Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))
How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA
Hi Anonymous ,
Does week1=5, week2=5 count the number of working days?(Exclude Saturday and Sunday)
Don't you want to count all these days?
If you are going to show your days per week, can you provide me with expected results(In table form)?
Here's the solution about count all working days(Exclude Saturday and Sunday).
Calendar table:
Calendar = ADDCOLUMNS(CALENDAR(DATE(2022,1,1),DATE(2022,12,31)),"WeekDay",WEEKDAY([Date],2))Count measure:
Days = COUNTROWS(FILTER('Calendar',[Date]>=MAX('Table'[start date])&&[Date]<=MAX('Table'[end date])&&[WeekDay]<>6&&[WeekDay]<>7))Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
The WEEKDAY function is not used correctly.
It should look like this.
WEEKDAY('Date'[Date],2)<>6&&WEEKDAY('Date'[Date],2)<>7Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Yes you're right, I've resolved it.
Anyway, that's not my issue. I would like to count the days per week.
That's a part of my table :
I also have a DATE Table with all days.
And I would like to know how many days each car (Immatriculation) has been used per week.
So, I can't do an histogram with Year/Weeknum and NbDays (and filter = Immatriculation)
