Calculate Availability For Various Dates

[vkondapa]

Hi, Can anyone help me with the following scenario.

I have a table like this below.

Device	StartDateTime	EndDateTime	StartDate	EndDate	Durationhours
Device A	2/7/2022 15:00	2/7/2022 16:00	2/7/2022	2/7/2022	1
Device B	2/7/2022 4:00	2/8/2022 4:00	2/7/2022	2/8/2022	24
Device C	2/7/2022 23:22	2/8/2022 19:46	2/7/2022	2/8/2022	x
Device A	2/8/2022 21:54	2/10/2022 21:56	2/8/2022	2/10/2022	x

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 A	X % Availability	X % Availability	X % Availability
Device B	X % Availability	X % Availability	X % Availability
Device C	X % Availability	X % Availability	X % Availability

[Anonymous · Accepted solution]

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

[vanessafvg]

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)