Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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.
Hi,
Share some data and show the expected result.
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.
Hi,
You may download my PBI file from here.
Hope this helps.
Fantastic!!!
It has served me and a lot.
I have it solved.
Thank you so much!!
You are welcome. If my reply helped, please mark it as Answer.
You are welcome.
Possibly Open Tickets, it is designed to deal with date intervals.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364
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:
User | Count |
---|---|
118 | |
66 | |
65 | |
56 | |
50 |
User | Count |
---|---|
181 | |
85 | |
67 | |
61 | |
53 |