Days Fleet Vehicle Available For Each Month

[hmbedford]

Hi All,

I am trying to calculate the days that a vehicle was available for each month that it was on fleet. Vehicles can span multiple months and cover partial months. I am pulling 12 months worth of data and here is a small example of the data.

Vehicle	Start_Fleet_Date	End_Fleet_Date
Car 1	20/Nov/2019	03/Mar/2020
Car 2	29/Sep/2019	26/Feb/2020
Car 3	10/Aug/2019	10/Jan/2020

For each vehicle i need to be able to calculate the days that the vehicle was available for each month that the data set spans. For example

Vehicle	Aug 19	Sep 19	Oct 19	Nov 19	Dec 19	Jan 20	Feb 20	Mar 20
Car 1	0	0	0	10	31	31	29	3
Car 2	0	2	31	30	31	31	26	0
Car 3	21	30	31	30	31	10	0	0

Once i have this i can then align to the bookings and have a monthly utilisation for each vehicle/model.

I am struggling how to calculate each individual month for each row of data.

Appreciate any help or suggestions

Thanks

Hayden

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[A1984944]

I work for a nonprofit school for children with autism and we have multiple vans for outings.
My boss has asked me to show data on whether or not we have to many vehicles and can we get rid of any.
I have the dates and number of times each vehicle was driven but I am not sure how to organize it. I did it by percentages and broke it out monthly for the last six months but that is not giving me what I need.

Here is one month of data but I'm not sure how to show the data on any vehicle can be gotten rid of. My vehicle data is van # in column 1 and dates along row 1 starting in column 2 :  4/10/2023       4/11/2023       4/12/2023       4/13/2023       4/14/2023       4/17/2023       4/18/2023       4/19/2023       4/20/2023       4/21/2023       4/24/2023       4/25/2023       4/26/2023       4/27/2023       4/28/2023


#1 (Acd)        17              6       2                       11      12      6                                       2       
#2 (Acd)        16      1       6       2       1       1       11      12      6               1               1       2       1
#5 (Acd)        16              6       2                       11      12      6                                       2       
#15 (Acd)                       1       1       8                       2       11      6                                       
#16 (Acd)                               1       8                               11      8                                       
#17 (Acd)                               1       8                               10      8                                       
#18 (LS)                1       1       2               1       1                               1               1               
#19 (EA)                                1       1               1                                       1                       
#23 (Res)       3       2       3       8       5                               1       2       4               4       6       4
#24 (Res)                                                                                                                       
#28 (Ls)                                                                                                                       
#29 (Res)       5       9       3       9       5       4       4       5       6       2       5       5       6       4       4

Thank you so much for your help in this.

[Syndicate_Admin]

Hello

I have a similar problem and the solution proposed would serve me but I do not have a table with each of the dates of each car, according to the period that has been active.

What I have is a table with all the cars, 1 in each row, and 2 columns with "high date" and "low date".

How do I get the days that each car has been active and each month of each year?

Thank you.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Syndicate_Admin]

Hello

I have a table similar to this

License plate	March	Registration Date	Low Date
0557LWD	VW LCV	02/02/2022 04:02:32
0706LNS	VOLKSWAGEN	26/05/2022 09:05:21
0825LWR	VW LCV	11/03/2022 10:03:48
0841LPK	VOLKSWAGEN	19/07/2022 03:07:34
1137LNJ	VW LCV	31/03/2021 04:03:27
0006LBN	VOLKSWAGEN	07/10/2019 06:10:09	04/09/2020 00:00
0007LBN	VOLKSWAGEN	10/10/2019 12:10:59	27/08/2020 00:00
0009LBN	VOLKSWAGEN	07/10/2019 06:10:49	31/08/2020 00:00
0010LBN	VOLKSWAGEN	10/10/2019 12:10:03	13/01/2021 00:00
0014LBN	VOLKSWAGEN	08/10/2019 12:10:44	04/07/2020 00:00
0056KMW	VOLKSWAGEN	23/10/2019 11:10:49	13/12/2019 00:00
5579LJK	VOLKSWAGEN	25/08/2020 04:08:49	08/10/2020 00:00
5585LNV	SEAT	14/03/2022 12:03:19	21/06/2022 00:00
5587LNV	SEAT	22/03/2022 03:03:25	13/05/2022 00:00

I replace the blank cells with the last day of the full month for which I have data, in this case 31/8/22.

I am interested in knowing about each car the days it has been active (difference between date of registration and date of cancellation), by year and month and to be able to compare it with the days it has been in use or rented. Usage or rental data comes from another table where the issued contracts are collected. In this way you would have the occupation or rate of use of each car, by day, week, month, year, etc.

The correct result of occupancy days by month and year should be something like this:

Year 2022
License plate	January	February	March	April	May	June	July	August	September
0557LWD	0	27	31	30	31	30	31	31
0706LNS	0	0	0	0	6	30	31	31
5585LNV	0	0	18	30	31	21	0	0
5587LNV	0	0	10	30	13	0	0	0

I hope I have been able to explain myself.

Thanks a lot.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Syndicate_Admin]

Fantastic!!!

It has served me and a lot.

I have it solved.

Thank you so much!!

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[hmbedford]

Hi @Ashish_Mathur 

This worked a treat, thenk you very much

Regards

Hayden

[Ashish_Mathur]

You are welcome.

[Greg_Deckler]

Possibly Open Tickets, it is designed to deal with date intervals. 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364

[camargos88]

Hi @hmbedford ,

First use this code on Edit Query -> Advanced Editor:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUjBU0lEyMtD3yy/TNzIwtATyDIz1fROLgDwjA6VYHYgqI5AqS/3g1AKYKiMzfbfUJFRVxkBxQwN9x9J0mCogzysxD6oqFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Vehicle = _t, Start_Fleet_Date = _t, End_Fleet_Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Vehicle", type text}, {"Start_Fleet_Date", type date}, {"End_Fleet_Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each List.Dates([Start_Fleet_Date], Duration.Days([End_Fleet_Date] - [Start_Fleet_Date])+1, #duration(1, 0, 0, 0))),
#"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Dates",{"Start_Fleet_Date", "End_Fleet_Date"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Dates", type date}})
in
#"Changed Type1"

After that, create this measure:

Qtd_Month =

VAR _start = CALCULATE(MIN('Table'[Dates]); FILTER(ALL('Date');'Date'[Year_Month] = SELECTEDVALUE('Date'[Year_Month])))

VAR _end = CALCULATE(MAX('Table'[Dates]); FILTER(ALL('Date');'Date'[Year_Month] = SELECTEDVALUE('Date'[Year_Month])))

VAR _result = INT(_end - _start)

RETURN IF(_result = BLANK(); 0; _result + 1)

I hope it helps,

Ricardo