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