Forum Discussion

hmbedford's avatar
hmbedford
Frequent Visitor
6 years ago
Solved

Days Fleet Vehicle Available For Each Month

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.

 

VehicleStart_Fleet_DateEnd_Fleet_Date
Car 120/Nov/201903/Mar/2020
Car 229/Sep/201926/Feb/2020
Car 310/Aug/201910/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

 

VehicleAug 19Sep 19Oct 19Nov 19Dec 19Jan 20Feb 20Mar 20
Car 1000103131293
Car 20231303131260
Car 321303130311000

 

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

Need Help

12 Replies

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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
     
    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      3 years ago

      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.

    • A1984944's avatar
      A1984944
      New Member
      2 years ago

      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.