cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marcp2 Frequent Visitor
Frequent Visitor

Counting items between dates day by day

Hi there

I try to manage a fleet of buses. I need to make a dashboard showing day by day the number of buses running vs the number of buses in the fleet.

 

I have two tables

Buses : with informations on each bus 

License plate, brand, type, date of first commercial operation, date of last commercial operation...

 

Journeys : with informations on trip operated by the buses fleet

Date of journey, License plate, line number, direction, start time, arrival time

 

I need a table showing something like this

date, number of buses having operated at least one journey in the day, number of buses in service (date between date of first commercial operation and date of last commercial operation).

 

Here is a very simplified example with a fleet of 2 buses

Buses

License plate

Brand

Type

First op

Last op

xx

A

A1

08/11/2017

10/11/2017

yy

B

B1

07/11/2017

11/11/2017

 

Journeys

Date

License plate

Line number

Direction

Start time

Arrival time

07/11/2017

yy

15

A

08:30

12:15

07/11/2017

yy

15

B

13:00

17:00

08/11/2017

xx

18

A

09:00

13:00

08/11/2017

xx

18

B

14:00

18:30

09/11/2017

yy

15

A

08:35

12:25

09/11/2017

yy

15

B

13:00

16:50

09/11/2017

xx

18

A

09:00

13:05

09/11/2017

xx

18

B

14:00

18:20

11/11/2017

yy

15

A

08:30

12:20

11/11/2017

yy

15

B

13:05

17:10

 

 

Buses in fleet

Buses running

06/11/2017

0

0

07/11/2017

1

1

08/11/2017

2

1

09/11/2017

2

2

10/11/2017

2

0

11/11/2017

1

1

12/11/2017

0

0

 

The license plate is the link between the buses and journeys table

I can't figure out the data model with a calendarauto table

I can't figure out the measures to properly count the number of buses in the fleet

 

Can you help me ?

Regards

Marc

 

1 REPLY 1
Super User
Super User

Re: Counting items between dates day by day

Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 497 members 3,688 guests
Please welcome our newest community members: