Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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!!

1 ACCEPTED SOLUTION

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.

vstephenmsft_0-1643090661513.png

 

The calendar table has dates, year-weeks, and a sort column sorted by the year-week column.

vstephenmsft_1-1643090717780.png

 

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)

vstephenmsft_2-1643091083189.png

 

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.

vstephenmsft_3-1643091174670.png

You can select cars based on the slicer to display results for different cars.

vstephenmsft_4-1643091223866.png

 

 

 

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.

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

Sorry,

It means I didn't be clear 😉

I would like the count of days per week! Not per record. Maybe it would be solved if I have the measure you sent to me.

 

Anyway, I tried to write the measure but i got an error message :

bapt69_0-1641466941143.png

 

My calendar TABLE is called DATE and the column dates [Date]

Hi @Anonymous ,

 

The WEEKDAY function is not used correctly.

vstephenmsft_0-1641520274277.png

It should look like this.

WEEKDAY('Date'[Date],2)<>6&&WEEKDAY('Date'[Date],2)<>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.

 

Anonymous
Not applicable

That's what I did today :

bapt69_0-1641568266849.png

The result is not really what I expected but it works... 😉 (note : 50days per week is not an issue cause many cars are used each week...so I actual must have more than 5 days per week - but not with only one car)

bapt69_1-1641568392484.png

 

When I used a filtrer on the Immatriculation, the result doesn't change...the COUNTROWS is not linked with what you use on the "Retour Véhicule" TABLE?

 

To be continued... 😉

Hi @Anonymous ,

 

I think I understand what you want. 

From your screenshot, I saw that you have a year-week column.

Do you want to count group by year and week?

Try

Measure =
COUNTROWS (
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        [YearWeek] = MAX ( 'Calendar'[YearWeek] )
            && [Other judgment conditions]
    )
)

 

 

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
Not applicable

Hi,

The count group by week (or year) is ok with my measure (DATE Table).

The slicer on the car (other table) is not ok.

 

I have all the car we used day by day (so week by week or year by year)...but, when I use the slicer "Immatriculation" (car reference), nothing 's happened. The result doesn't change.

Hi @Anonymous ,

 

Can you provide your formula?

Did you use the ALLSELCTED function? The reason the Immatriculation slicer doesn't work may be because of using the ALL function.

vstephenmsft_0-1642143327508.png

vstephenmsft_1-1642143334546.png

 

 

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
Not applicable

My formula still not working :

UtilVéhicule = COUNTROWS(
                              Filter(
                                   ALLSELECTED('Retour véhicule'),
                                      'Retour véhicule'[Datededépart]<='Date'[Date]&&
                                      'Retour véhicule'[Datederetour]>='Date'[Date]))
 
work in progress lol....

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.

vstephenmsft_0-1643090661513.png

 

The calendar table has dates, year-weeks, and a sort column sorted by the year-week column.

vstephenmsft_1-1643090717780.png

 

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)

vstephenmsft_2-1643091083189.png

 

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.

vstephenmsft_3-1643091174670.png

You can select cars based on the slicer to display results for different cars.

vstephenmsft_4-1643091223866.png

 

 

 

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
Not applicable

Oh thanx, I did it.

That's what you said is working. That's a step. But it does not work for ALL THE CARS.

 

Is it not possible to visualise an histogram with all result AND using the slicer?...therefore you got the result for all cars and for each cars (or several cars) if you need.

I got both of result but not in the same Measure/graph 😄 

 

Anyway, I quit.

Thank you for all your support about this request! That's great!!

Anonymous
Not applicable

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 :

bapt69_0-1641567026209.png

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)

v-stephen-msft
Community Support
Community Support

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))

 

vstephenmsft_0-1641461554523.png

 

 

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.

amitchandak
Super User
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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.