Forum Discussion

vkondapa's avatar
vkondapa
New Member
4 years ago
Solved

Calculate Availability For Various Dates

Hi, Can anyone help me with the following scenario.

I have a table like this below.

DeviceStartDateTimeEndDateTimeStartDateEndDateDurationhours
Device A2/7/2022 15:002/7/2022 16:002/7/20222/7/20221
Device B2/7/2022 4:002/8/2022 4:002/7/20222/8/202224
Device C2/7/2022 23:222/8/2022 19:462/7/20222/8/2022x
Device A2/8/2022 21:542/10/2022 21:562/8/20222/10/2022x

From the table, I need to calculate total hours that is available apart from start and end time. For suppose in the first row, Device A started and ended between 3PM to 4PM(1 hour). So my availability will be 24hour duration - 1hour. If dates are different, like shown in 2nd and 3rd rows, it have to calculate availability for 2 dates and 

I need to get an output Like below in a matrix visual.

For 2/9/2022 there's no data. So it should show 100% availability.

 

I used following formulas to calculate startdate and enddate availability. But unable to figure out how to add these and show in single column.

 

availabilityHoursStartDate = if(table[startdate]<table[enddate],(hour(table[startdate])+(minute(table[startdate])/60)+(SECOND(table[startdate])/3600))-00,24-(table[startdate][Duration_Hours]))
 
availabilityHoursEndDate = if(table[startdate]<table[enddate],24-(hour(table[enddate])+(minute(table[enddate])/60)+(SECOND(table[enddate])/3600)),24-table[Duration_Hours])
 

 

Device id

2/7/2022

 

2/8/2022

 

2/9/2022

 

Device AX % AvailabilityX % AvailabilityX % Availability
Device BX % AvailabilityX % AvailabilityX % Availability
Device CX % AvailabilityX % AvailabilityX % Availability
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi vkondapa ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want.

    1. Process the table data in Power Query Editor

    • Convert multiple rows base on StartDateTime and EndDateTime
    • Add a custom column to get the duration between the "New" StartDateTime and EndDateTime

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wckkty0xOVXBU0lEy0jfXNzIwMlIwNLUyMEARMEMVQGbG6sANcULWYwLTYoHGRzLCAsMIZ2QjjIytUBQqGFpamZgRNsQRWY+RoZWpCVjA0AAhYoasFUlSKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Device = _t, StartDateTime = _t, EndDateTime = _t, StartDate = _t, EndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Device", type text}, {"StartDateTime", type datetime}, {"EndDateTime", type datetime}, {"StartDate", type date}, {"EndDate", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Device", Order.Ascending}, {"StartDateTime", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Dates", each [a={Number.RoundUp(Number.From([StartDateTime]))..Number.RoundDown(Number.From([EndDateTime]))},b=List.Transform(List.Zip({{[StartDateTime=[StartDateTime]]}&List.Transform(a,each[StartDateTime=DateTime.From(_)]),List.Transform(a,each[EndDateTime=DateTime.From(_)-#duration(0,0,0,1)])&{[EndDateTime=[EndDateTime]]}}),Record.Combine)][b]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"StartDateTime", "EndDateTime"}),
    #"Expanded Dates" = Table.ExpandListColumn(#"Removed Columns", "Dates"),
    #"Expanded Dates1" = Table.ExpandRecordColumn(#"Expanded Dates", "Dates", {"StartDateTime", "EndDateTime"}, {"StartDateTime", "EndDateTime"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Dates1", "Duration", each if DateTime.Time([StartDateTime])=#time(0,0,0) and  DateTime.Time([EndDateTime])=#time(23,59,59) then 24 else Duration.Hours([EndDateTime]-[StartDateTime])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Duration", type number}})
in
    #"Changed Type1"

     

    2. Create a date dimension table

     

    Date = CALENDAR(DATE(2022,2,1),TODAY())

     

    3. Create a measure [Availability]as below to get the availability%

     

    Availability = 
VAR _usedhours =
    CALCULATE (
        SUM ( 'Table'[Duration] ),
        FILTER (
            'Table',
            DATE ( YEAR ( 'Table'[StartDateTime] ), MONTH ( 'Table'[StartDateTime] ), DAY ( 'Table'[StartDateTime] ) )
                = SELECTEDVALUE ( 'Date'[Date] )
        )
    )
RETURN
    IF ( ISBLANK ( _usedhours ), BLANK (), DIVIDE ( 24 - _usedhours, 24, 0 ) )

     

    4. Create a matrix visual(Rows: Devices  Columns: Date field of date dimension table  Values: measure [Availability] )

    Best Regards

2 Replies

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    4 years ago

    it might be best to create a column with the hours in it and then work it out from there.  not 100% clear on what you want to end up with 

     

    create this column and use this to derive your availability 

    Difference Time = DATEDIFF(Test[StartDateTime], Test[EndDateTime], HOUR)

     

     
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi vkondapa ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want.

    1. Process the table data in Power Query Editor

    • Convert multiple rows base on StartDateTime and EndDateTime
    • Add a custom column to get the duration between the "New" StartDateTime and EndDateTime

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wckkty0xOVXBU0lEy0jfXNzIwMlIwNLUyMEARMEMVQGbG6sANcULWYwLTYoHGRzLCAsMIZ2QjjIytUBQqGFpamZgRNsQRWY+RoZWpCVjA0AAhYoasFUlSKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Device = _t, StartDateTime = _t, EndDateTime = _t, StartDate = _t, EndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Device", type text}, {"StartDateTime", type datetime}, {"EndDateTime", type datetime}, {"StartDate", type date}, {"EndDate", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Device", Order.Ascending}, {"StartDateTime", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Dates", each [a={Number.RoundUp(Number.From([StartDateTime]))..Number.RoundDown(Number.From([EndDateTime]))},b=List.Transform(List.Zip({{[StartDateTime=[StartDateTime]]}&List.Transform(a,each[StartDateTime=DateTime.From(_)]),List.Transform(a,each[EndDateTime=DateTime.From(_)-#duration(0,0,0,1)])&{[EndDateTime=[EndDateTime]]}}),Record.Combine)][b]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"StartDateTime", "EndDateTime"}),
    #"Expanded Dates" = Table.ExpandListColumn(#"Removed Columns", "Dates"),
    #"Expanded Dates1" = Table.ExpandRecordColumn(#"Expanded Dates", "Dates", {"StartDateTime", "EndDateTime"}, {"StartDateTime", "EndDateTime"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Dates1", "Duration", each if DateTime.Time([StartDateTime])=#time(0,0,0) and  DateTime.Time([EndDateTime])=#time(23,59,59) then 24 else Duration.Hours([EndDateTime]-[StartDateTime])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Duration", type number}})
in
    #"Changed Type1"

     

    2. Create a date dimension table

     

    Date = CALENDAR(DATE(2022,2,1),TODAY())

     

    3. Create a measure [Availability]as below to get the availability%

     

    Availability = 
VAR _usedhours =
    CALCULATE (
        SUM ( 'Table'[Duration] ),
        FILTER (
            'Table',
            DATE ( YEAR ( 'Table'[StartDateTime] ), MONTH ( 'Table'[StartDateTime] ), DAY ( 'Table'[StartDateTime] ) )
                = SELECTEDVALUE ( 'Date'[Date] )
        )
    )
RETURN
    IF ( ISBLANK ( _usedhours ), BLANK (), DIVIDE ( 24 - _usedhours, 24, 0 ) )

     

    4. Create a matrix visual(Rows: Devices  Columns: Date field of date dimension table  Values: measure [Availability] )

    Best Regards