Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.