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 wort...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

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