Forum Discussion
Count days for each week
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.
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)
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.