Summarizing total days where a specific count of vehicles are needed

[CMaxwell]

Hello,

I posted on this topic previously but was not able to include the data set due to the size and complexity.  I want to try this again but in a simpler form.  I am trying to show the total number of days from a date range where a specifc total of vehicles are needed.  Here is the dataset I am working with.  The column chart is what I am trying to create in Power BI where I summarize the vehicle count for each individual date, and then summarize the number of days where a sp

ecific vehicle count is needed (1-5 vehicles).  I was able to create the visual in excel by using a pivot table, but I want to set this up in Power BI to make updating easier. 

I'm still learning Power BI and guessing a measure is needed, but not sure how to do that.  Is there a simple way to summarize and visualize the data and generate the chart below?

Drive ID	Drive Date		Vehicle Need
1	1/1/2024		1
2	1/1/2024		1
3	1/1/2024		1
4	1/2/2024		2
5	1/2/2024		1
6	1/3/2024		1
7	1/3/2024		1
8	1/3/2024		1
9	1/3/2024		1
10	1/4/2024		1
11	1/4/2024		2
12	1/4/2024		1
13	1/5/2024		2
14	1/8/2024		1
15	1/8/2024		1
16	1/9/2024		2
17	1/9/2024		2
18	1/9/2024		1
19	1/10/2024		1
20	1/10/2024		2
21	1/10/2024		1
22	1/11/2024		3
23	1/12/2024		1
24	1/12/2024		1
25	1/12/2024		1
26	1/12/2024		1
27	1/14/2024		1
28	1/15/2024		1
29	1/16/2024		2
30	1/16/2024		1
31	1/16/2024		1
31	1/17/2024		5

[Anonymous]

Hi @CMaxwell ,

Hello! I wanted to check if your issue has been resolved. I noticed that @SamWiseOwl  has provided two excellent solutions. If either of these has addressed your concern, please consider marking it as the accepted solution, as this can be immensely helpful for others facing similar challenges. However, if the problem is still unresolved, please let us know—we’ll be glad to assist further.

Best Regards,
Bof

[SamWiseOwl]

Hi @CMaxwell 

The easiest method would be using the query editor.

Right click the date column and choose Group By and then Sum the Cars needed column:

[SamWiseOwl · Accepted solution]

The other option is create a list of numbers to represent number of cars.

Either using Enter Data or GenerateSeries.

Use this in you visauls with this measure:

Car Days Measure =

var curr = SELECTEDVALUE('Total needed Table'[Number needed]) --capture current column number of cars needed.

RETURN

COUNTROWS(--count the number of rows

    Filter( --filter to current column nummber

        SUMMARIZE(--group the table by date, sum number of cars needed

                    all('Car Table'), 'Car Table'[Drive Date], "New", SUM('Car Table'[Vehicle Need]))

                    ,[New] = curr))