Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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!!
Solved! Go to 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.
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.
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 :
My calendar TABLE is called DATE and the column dates [Date]
Hi @Anonymous ,
The WEEKDAY function is not used correctly.
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.
That's what I did today :
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)
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.
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.
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.
My formula still not working :
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.
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!!
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)
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.
@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
User | Count |
---|---|
121 | |
69 | |
67 | |
57 | |
50 |
User | Count |
---|---|
176 | |
83 | |
69 | |
65 | |
54 |