Reply
Occasional Visitor
Posts: 1
Registered: ‎12-07-2018

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

 

Super User
Posts: 10,536
Registered: ‎07-11-2015

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


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

Proud to be a Datanaut!